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

[James Newman]

Help needed with excel formula.  I have an excel document with 4 sheets. I want sheet1 to add up data from sheet3.  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.  Can anyone help with a formula please????

[#2]

Hi James,

Could you please provide small sample file to be concrete with possible solutions.

[#3]

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

[#4]

James, thank you.

So, in sheet Totals only serial numbers from sheet CRIS with status Open and nothing more?

[#5]

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”

[#6]

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.

[#7]

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

[#8]

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.

[#9]

Many thanks for your help, seems to have done the trick, thanks