Sum If Two Conditions Are Met

[ExcelGuruWannaBe]

I am trying to write a formula that will look at two conditions. I currently have valid data in cells A & I 1-538. Cells A & I 539-1038 have either 0 or N/A that I will use for future adds. Any way I can get this formula to work with that? It currently returns an N/A.

=SUM((--(Master!$A$3:$A$1038="X")*--(Master!$I$3:$I$1038="HIGH")))

Thanks,
WannaBe

 

[machopicho]

=SUMPRODUCT((--(Master!$A$3:$A$1038="X")*--(Master!$I$3:$I$1038="HIGH")))

 

[ExcelGuruWannaBe]

Still gives me an N/A. Any other suggestions?

 

[Asator]

What are you actually adding? If you just want to count the number of records that match the conditions, this will work:
=sumproduct(--(range1=condition1),--(range2=condition2))

If you want to sum values associated with those ranges, use:
=sumproduct(--(range1=condition1),--(range2=condition2),range3)

 

[NBVC]

Try:

=SUMPRODUCT(--ISNUMBER(MATCH("X",Master!$A$3:$A$1038,0)),--ISNUMBER(MATCH("HIGH",Master!$I$3:$I$1038,0)))

 

[Asator]

nbvc: wouldn't using match like that only return one result?

 

[ExcelGuruWannaBe]

Thanks!

I am trying to count the number of times that both conditions are met.

Asator - I am now getting a #value error

NBVC - I keep getting 1 as the response.

Thanks for all of your help!

 

[NBVC]

Yes, probably should've been more like:

 =SUMPRODUCT(--ISNUMBER(MATCH(Master!$A$3:$A$1038,{"X"},0)),--ISNUMBER(MATCH(Master!$I$3:$I$1038,{"HIGH"},0)))

wasn't thinkin straight enough...

 

[Cease]

You might also be able to use a sumifs or countifs function, depending what you need.

If the value you want to sum is based on meeting either criteria you could use this, where column J would have the actual values that would be added:
=sumifs(J3:Master!j1038,Master!A3:A1038,"X",Master!I3:I1038,"High")

But if you just wanted to count the amount of cells with X or High depending you could use this:
=Countifs(Master!A3:A1038,"X",Master!I3:I1038,"High")

This may or may not cover what you're looking for but I hope it helps!

 

[Asator]

Post some of your data, and the results you're looking for, please.