SUMPRODUCT Difficulty

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

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

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

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

Opps,
use this formula I made a mistake before

=sumproduct((B153:b182="yes")+(B153:B182="No")+(B1 53:b182="Activity")+(B153:B1820))



"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

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

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............................


"Eduardo" wrote in message
...
Opps,
use this formula I made a mistake before

=sumproduct((B153:b182="yes")+(B153:B182="No")+(B1 53:b182="Activity")+(B153:B1820))



"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

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

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

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

"Eduardo" wrote in message
...
Opps,
use this formula I made a mistake before

=sumproduct((B153:b182="yes")+(B153:B182="No")+(B1 53:b182="Activity")+(B153:B1820))



"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