SUMPRODUCT Difficulty

Luke,

Thank you for taking the time to respond to my problem. Unfortunately, I
still could not get the desired answer. Ken Wright's solution did the
trick.

Thanks again for your help.
Pam
"Luke M" wrote in message
...
Good catch. Could add an ISNUMBER check to prevent text entries.

=SUMPRODUCT(((B153:B182="Yes")+(B153:B182="No")+(B 153:B182="Activity")+(ISNUMBER(B153:B182))*(B153:B 1820)),--(A153:A182="Vendor"))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ken Wright" wrote:

Try a simple test, assuming say cell B181 in your data = "Goal", and put
in
any other cell =B1810

You will get back the answer TRUE

Now rationalise that with what's in your formula.

Your last array in the first section, ie "+(B153:B1820)" will return a
positive count for every text entry in the data, doubling up with the
required ones that you have already tested for, and also adding in the
undesired entry "Goal"

Regards
Ken...........................


"Luke M" wrote in message
...
Your formula will always return 0. (you find if yes, then multiplied if
no.
Same cells can not be both!) Arrays need to be added together (like OP
did
with original COUNTIFs)

=SUMPRODUCT(((B153:B182="yes")+(B153:B182="No")+(B 153:B182="Activity")+(B153:B1820)),--(A153:A182="Vendor"))

This counts number of yes, no, Activity, and 0 in rows that have
"Vendor"
in column A.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Eduardo" wrote:

Hi Pam
try
=sumproduct(--(B153:b182="yes"),--(B153:B182="No"),--(B153:b182="Activity"),--(B153:B1820)

if this helps please click yes, thanks

"Pam" wrote:

Hi,

After reading thru several posts and figuring out that I could not
use
COUNTIF on non-adjacent cells, I tried using SUMPRODUCT but am still
having
trouble getting it to calculate as needed. Below is an example of
what
I
need to happen.

Col A Col B
Vendor Activity
Booking 0
Vendor 500
Booking 0
Vendor Goal
Booking 100

Every other line has a vendor with a booking for that vendor beneath
it. I
need to count only the vendor rows with amounts greater than 0, the
word
"activity", the word "yes" and the word "no". I don't need it to
count
"goal" that will be counted in another cell.

This is the formula I used before the Booking line was entered after
each
vendor and it worked great:

=COUNTIF(C153:C181,"0")+COUNTIF(C153:C181,"Yes")+ COUNTIF(C153:C181,"No")+COUNTIF(C153:C181,"activit y")

So far, using =SUMPRODUCT(--(MOD(ROW(B153:B182),2)=1),--(B153:B182
0)), I
get a total of everything - amounts, 0's and any text entered. I've
tried
using SumProduct like the CountIf above, breaking into individual
sections,
but that gives duplicates the amounts to give an inflated total.

If anyone can please tell me how to use SUMPRODUCT or some other
alternative, to achieve desired results, I would greatly appreciate
it.

Thanks in advance,
Pam

You're very welcome Pam, and thank you for letting us know :-)

Regards
Ken......................

"Pam" wrote in message
...
Ken,

That's it!! Thank you for taking the time to help with my problem. I
entered so many different variations trying to get it to work and your
solution was short and very clear.

Thanks again,
Pam

"Ken Wright" wrote in message
...
Assuming the only possible entries against Vendor that you DONT want
counted are Goal and 0, then how about:-

=SUMPRODUCT((A153:A181="Vendor")*(B153:B181"Goal ")*(B153:B1810))

Regards
Ken............................


"Pam" wrote in message
...
Hi,

After reading thru several posts and figuring out that I could not use
COUNTIF on non-adjacent cells, I tried using SUMPRODUCT but am still
having trouble getting it to calculate as needed. Below is an example of
what I need to happen.

Col A Col B
Vendor Activity
Booking 0
Vendor 500
Booking 0
Vendor Goal
Booking 100

Every other line has a vendor with a booking for that vendor beneath it.
I need to count only the vendor rows with amounts greater than 0, the
word "activity", the word "yes" and the word "no". I don't need it to
count "goal" that will be counted in another cell.

This is the formula I used before the Booking line was entered after
each vendor and it worked great:

=COUNTIF(C153:C181,"0")+COUNTIF(C153:C181,"Yes")+ COUNTIF(C153:C181,"No")+COUNTIF(C153:C181,"activit y")

So far, using =SUMPRODUCT(--(MOD(ROW(B153:B182),2)=1),--(B153:B182 0)),
I get a total of everything - amounts, 0's and any text entered. I've
tried using SumProduct like the CountIf above, breaking into individual
sections, but that gives duplicates the amounts to give an inflated
total.

If anyone can please tell me how to use SUMPRODUCT or some other
alternative, to achieve desired results, I would greatly appreciate it.

Thanks in advance,
Pam