SUMPRODUCT Difficulty

[Pam]

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,"activity")

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

 

[Ken Wright]

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:B181<>0))

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

 

[Eduardo]

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

if this helps please click yes, thanks

 

[Luke M]

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")+(B153:B182="Activity")+(B153:B182>0)),--(A153:A182="Vendor"))

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

 

[Eduardo]

Opps,
use this formula I made a mistake before

=sumproduct((B153:b182="yes")+(B153:B182="No")+(B153:b182="Activity")+(B153:B182>0))

 

[Ken Wright]

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

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:B182>0)" 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...........................

 

[Ken Wright]

Assuming you used some dummy data to test with, make every entry in col B
the undesired word "Goal" and take a look at the result. Now make every
entry the word "Yes" and look at that result.

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

 

[Luke M]

Good catch. Could add an ISNUMBER check to prevent text entries.

=SUMPRODUCT(((B153:B182="Yes")+(B153:B182="No")+(B153:B182="Activity")+(ISNUMBER(B153:B182))*(B153:B182>0)),--(A153:A182="Vendor"))

 

[Pam]

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

 

[Pam]

Eduardo,

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

 

[Pam]

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

 

[Ken Wright]

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

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