| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Difficulty
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............................ "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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Difficulty
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Difficulty
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Difficulty
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Difficulty
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Difficulty
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Difficulty
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Difficulty
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:B181<0)) 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Difficulty
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT Difficulty
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:B181<0)) 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Function Difficulty | Excel Discussion (Misc queries) | |||
| Formula difficulty | Excel Discussion (Misc queries) | |||
| Difficulty in transposing | New Users to Excel | |||
| VLOOKUP Difficulty | Excel Discussion (Misc queries) | |||
| IF Statement difficulty | Excel Worksheet Functions | |||
![Pam[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
