Sumproduct to populate 0 if cell is blank

[tinarexrode3]

currently using
=SUMPRODUCT((R1:R25='CONTRACT VENDORS'!A3:A712)*1)

If a "R" cell is blank I need it to populate a 0 however with the *1 I am always returning a 1.

This is probably an easy fix, but I'm a newbie with using formulas in excel.
Thanks for any help

 

[Claus Busch]

Hi,

Am Fri, 19 Oct 2012 11:06:07 -0700 (PDT) schrieb (e-mail address removed):

currently using
=SUMPRODUCT((R1:R25='CONTRACT VENDORS'!A3:A712)*1)

try:
=SUMPRODUCT(--('Contract Vendors'!$A$3:$A$712=R1),--('Contract
Vendors'!$A$3:$A$712<>""))
or:
=COUNTIF('Contract Vendors'!$A$3:$A$712,R1)


Regards
Claus Busch

 

[Matthew Dyer]

currently using =SUMPRODUCT((R1:R25='CONTRACT VENDORS'!A3:A712)*1) Ifa "R" cell is blank I need it to populate a 0 however with the *1 I am always returning a 1. This is probably an easy fix, but I'm a newbie with using formulas in excel. Thanks for any help

or you could also try:
=IF(COUNTA(R1:R25)<>25, 0, SUMPRODUCT((R1:R25='CONTRACT VENDORS'!A3:A712)*1))

there are other variations we could use depending on if there is a header row or if there would be only numerical values.