Ignoring blanks in a sumproduct formula

[guardian4119]

HI,

I have this formula but it keeps returning #VALUE because there are some blank cells in AW40:AW53. How do i get it to ignore the blanks? Thanks

=SUMPRODUCT((C40:C53=C6)*(AW40:AW53)*(G40:G53))

 

[arthurbr]

Are these blanks real blanks or do they contain a null text string ("") ?
Try =SUMPRODUCT((C40:C53=C6)*(AW40:AW53<>"")*(G40:G53))

 

[Phuoc]

Try:

=SUMPRODUCT(--(C40:C53=C6),AW40:AW53,G40:G53)

 

[guardian4119]

The blanks contain a null string text (""). This is where i get the error

 

[DanteAmor]

If you have a space, you can put it inside the quotes:

=SUMPRODUCT((C40:C53=C6)*(AW40:AW53<>" ")*(G40:G53))

Or try the following array formula.

[TABLE="class: grid, width: 500"] <tbody>[TR] [TD]{=SUM(IFERROR(IF(C40:C53=C6,IF(AW40:AW53,G40:G53)),0))}

<tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself