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