Sumproduct to populate 0 if cell is blank

  • Thread starter Thread starter tinarexrode3
  • Start date Start date
T

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
 
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
 
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.
 
Back
Top