Forum Discussion
Formula to add up across sheets and to identify data in two different columns
Hi James,
Could you please provide small sample file to be concrete with possible solutions.
Eg. I want to add all serial numbers that match in column D (eg 199660) that are also shown as Open in column I but to display the result on the sheet named totals
James, thank you.
So, in sheet Totals only serial numbers from sheet CRIS with status Open and nothing more?
I want to add up all the ones that are shown as “open” in column I for all the ones with matching serial numbers in column D.
so column D will have a 6 digit number in the box and I want to know how many with the matching 6 digit number are shown as a status of “open” in column I, but with the result showing in a box on the sheet that is named “totals”
To clarify, you have repeated serial numbers in D with status Open or Closed in I. You'd like to pick-up list of unique serial numbers with status Open. For each such serial number to calculate how many times it appears in the sheet and put summary result in sheet Totals in any form.
If so you may generate Pivot Table on your first sheet, apply Open as filter and add Serial numbers to rows and count of them to values.
Alternatively you may do that with formulas, let say in G2 of totals
=IFERROR(LOOKUP(2,1/(COUNTIF($G$1:G1,CRIS!$B$2:$B$900)=0)/(CRIS!$E$2:$E$900="Open"),CRIS!$B$2:$B$900),"")
and in H2
=SUMPRODUCT((CRIS!$B$2:$B$900=G2)*(CRIS!$E$2:$E$900="Open"))
drag them down till empty cells. Please see attached.