Formula to add up across sheets and to identify data in two different columns

Question

Help needed with excel formula. &nbsp;I have an excel document with 4 sheets. I want sheet1 to add up data from sheet3. &nbsp;I want it to add up all the items in column D with matching serial numbers eg 123456 that are also shown as “open” in column I. &nbsp;Can anyone help with a formula please????

sergeibaklan · Answer

Hi James,
&nbsp;
Could you please provide small sample file to be concrete with possible solutions.

james newman · Answer

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

sergeibaklan · Answer

James, thank you.
&nbsp;
So, in sheet Totals only serial numbers from sheet CRIS with status Open and nothing more?

james newman · Answer

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.&nbsp;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”

sergeibaklan · Answer

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.&nbsp;
&nbsp;
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.
&nbsp;
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.

Forum Discussion

Formula to add up across sheets and to identify data in two different columns

Share

Resources