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 :

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Friday, October 19, 2012 11:06:07 AM UTC-7, wrote:
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

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.