Forum Discussion
Formula to add up across sheets and to identify data in two different columns
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.
This is a copy of the totals sheet, the serial numbers are all listed in column E and I want it to count for each serial number those that are open on the cris sheet and entering the total in column F on the totals sheet next to the relevant serial number in column E. Then do the same for the closed ones into column G of the totals sheet. Hope that makes sense. Thanks for your help so far
James, you may use
=COUNTIFS(CRIS!$B$2:$B$20,$E2,CRIS!$E$2:$E$20,"Open")
or
=SUMPRODUCT(($E2=CRIS!$B$2:$B$20)*(CRIS!$E$2:$E$20="Open"))
first one is more preferable from performance point of view.
Both are in attached file.