sumproduct/countif function help

[kilikab]

I have 2 sheets in this Excel spread sheet. One is called "Main" and the
other "Resolve". I have already figured out a formula that I am using on
the main page to look at the "Resolve" sheet and count how many of each
versions we currently have with the below formula:

=COUNTIF('RESOLVE'!D2196,"V2")

now what I would like to do is look at all "V2" and to see if the RFI
field associated with each "V2" has a value in it and count those up. I
was trying to use the below formula:

=SUMPRODUCT(('RESOLVE'!D2196="V2")*('RESOLVE'!I2:I196<>""))

This doesn't seem to be working. Can anyone please help me out with
this. Thanks!

-kilikab

 

[Carim]

Hi,

=SUMPRODUCT(('RESOLVE'!D2196=V2)*--('RESOLVE'!I2 :I196<>""))

I am guessing column I is a column with non numeric entries ...

HTH
Cheers
Carim

 

[Cutter]

Try this:

=SUMPRODUCT(--(RESOLVE!D2196="V2"),--(RESOLVE!I2 :I196<>""))

 

[Bob Phillips]

=SUMPRODUCT(('RESOLVE'!D2196=V2)*('RESOLVE'!I2 :I196<>""))

or

=SUMPRODUCT(--('RESOLVE'!D2196=V2),--('RESOLVE'!I2 :I196<>""))

but no need for both like

=SUMPRODUCT(('RESOLVE'!D2196=V2)*--('RESOLVE'!I2 :I196<>""))




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Aladin Akyurek]

kilikab wrote:
....

now what I would like to do is look at all "V2" and to see if the RFI
field associated with each "V2" has a value in it and count those up. I
was trying to use the below formula:

=SUMPRODUCT(('RESOLVE'!D2196="V2")*('RESOLVE'!I2:I196<>""))

This doesn't seem to be working.

....

Do you get a wrong count or an error?