Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT, SUM(IF(...), or some combination?
Given the following dummy data (my real data involves hundreds of rows,
hundreds of color columns, and multiple "rank" columns): A B C D E F G H 1 Rank Colors red yellow blue green orange 2 John High 2 Y Y 3 Paul Med 0 4 George Low 1 Y 5 Ringo Low 2 Y Y 6 Total 1 0 1 2 1 I'm trying to come up with a formula (without macros) that will calculate the number of colors associated with a Low ranking Beatle (result should be 2 because given the data above, there are two colors (green and orange) associated with the two Beatles with a rank of "Low". Note that there are no colors associated with Paul and no Beatles associated with Yellow. So, SUMPRODUCT(($B$2:$B$5="Low")*($C$2:$C$50)) appears accurate, but won't give me the right answer for "High" Beatles because it is essentially a row count where colors 0 for a given Rank. I would expect the following values: Rank Colors High 2 Med 0 Low 2 Any ideas? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF or SUMPRODUCT to total cells containing multiple texts | Excel Worksheet Functions | |||
SUMPRODUCT or SUMIF | Excel Discussion (Misc queries) | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
SUMIF - HLOOKUP Combination | Excel Worksheet Functions | |||
Sumif not Sumproduct | Excel Worksheet Functions |