SUMPRODUCT formula for multiple column criteria not adding

[sjha]

Hi,

I am trying to sum a column based on two criterias from two different columns. Below is the formula I am trying to use:

=SUMPRODUCT(('GROSS Contracts'!B5:B191="GROSS")*('GROSS Contracts'!C5:C191="Goldservice")*'GROSS Contracts'!D5:D253)

If Col B has a word GROSS and Col C has Goldservice, I want Excel to SUM all the numbers in Col D.

Formula above is returning #N/A. I have trying in all different ways but now working. Can you please help? Thank you.

 

[Jonmo1]

Ranges in SUMPRODUCT formulas MUST be same size...
also, the SUM range should not be multiplied, just a comma before the Sum Range should be used.

try

=SUMPRODUCT(('GROSS Contracts'!B5:B191="GROSS")*('GROSS Contracts'!C5:C191="Goldservice"),'GROSS Contracts'!D5:D191)

 

[NBVC]

Your ranges are not the same size

try:

=SUMPRODUCT(('GROSS Contracts'!B5:B191="GROSS")*('GROSS Contracts'!C5:C191="Goldservice")*'GROSS Contracts'!D5:D191)

 

[sjha]

Awesome..life saver!!! Thank you very much!!!