Read through many post. This seems to be the one I need but it is failing me.
I keep getting a sum of 0. It will work if remove all but one criteria,
anymore I get sum 0. The sum should be 6 Occurrences..................
"Wc"=2 "S"=2 "L"=1 "V"=1

What am I doing wrong?


=SUMPRODUCT(--(C4:AG4="S"),--(C4:AG4="L"),--(C4:AG4="V"),--(C4:AG4="WC"))

Your formula does not calculate OR S OR L etc, it calculates AND and thus
cannot return the correct answer

try

=SUMPRODUCT(--((C4:AG4="S")+(C4:AG4="L")+(C4:AG4="V")+(C4:AG4="W C")0))

or

=SUMPRODUCT((C4:AG4="S")+(C4:AG4="L")+(C4:AG4="V") +(C4:AG4="WC"))


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"macamarr" wrote in message
...
Read through many post. This seems to be the one I need but it is failing
me.
I keep getting a sum of 0. It will work if remove all but one criteria,
anymore I get sum 0. The sum should be 6
Occurrences..................
"Wc"=2 "S"=2 "L"=1 "V"=1

What am I doing wrong?


=SUMPRODUCT(--(C4:AG4="S"),--(C4:AG4="L"),--(C4:AG4="V"),--(C4:AG4="WC"))

Hi,

You need to use the sum instead of the the sumproduct.

{=SUM(--(C4:AG4="S"),--(C4:AG4="L"),--(C4:AG4="V"),--(C4:AG4="WC"))}

The sumproduct was multiplying the answers ie (010), (100), (001) = (000)
where as the sum adds them.

Don't forget it is an array formula. {}=Ctrl+Shft+Enter
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"macamarr" wrote:

Read through many post. This seems to be the one I need but it is failing me.
I keep getting a sum of 0. It will work if remove all but one criteria,
anymore I get sum 0. The sum should be 6 Occurrences..................
"Wc"=2 "S"=2 "L"=1 "V"=1

What am I doing wrong?


=SUMPRODUCT(--(C4:AG4="S"),--(C4:AG4="L"),--(C4:AG4="V"),--(C4:AG4="WC"))

One mo

=sum(countif(c4:ag4,{"s","l","v","wc"}))


macamarr wrote:

Read through many post. This seems to be the one I need but it is failing me.
I keep getting a sum of 0. It will work if remove all but one criteria,
anymore I get sum 0. The sum should be 6 Occurrences..................
"Wc"=2 "S"=2 "L"=1 "V"=1

What am I doing wrong?

=SUMPRODUCT(--(C4:AG4="S"),--(C4:AG4="L"),--(C4:AG4="V"),--(C4:AG4="WC"))

--

Dave Peterson

Thank You All, works great and problem solved.

"Dave Peterson" wrote:

One mo

=sum(countif(c4:ag4,{"s","l","v","wc"}))


macamarr wrote:

Read through many post. This seems to be the one I need but it is failing me.
I keep getting a sum of 0. It will work if remove all but one criteria,
anymore I get sum 0. The sum should be 6 Occurrences..................
"Wc"=2 "S"=2 "L"=1 "V"=1

What am I doing wrong?

=SUMPRODUCT(--(C4:AG4="S"),--(C4:AG4="L"),--(C4:AG4="V"),--(C4:AG4="WC"))

--

Dave Peterson

And remember that this kind of thing will work, too:

=countif(c4:ag4,"s") + countif(c4:ag4,"l") + ....



macamarr wrote:

Thank You All, works great and problem solved.

"Dave Peterson" wrote:

One mo

=sum(countif(c4:ag4,{"s","l","v","wc"}))


macamarr wrote:

Read through many post. This seems to be the one I need but it is failing me.
I keep getting a sum of 0. It will work if remove all but one criteria,
anymore I get sum 0. The sum should be 6 Occurrences..................
"Wc"=2 "S"=2 "L"=1 "V"=1

What am I doing wrong?

=SUMPRODUCT(--(C4:AG4="S"),--(C4:AG4="L"),--(C4:AG4="V"),--(C4:AG4="WC"))

--

Dave Peterson


--

Dave Peterson