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