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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT, SUM(IF(...), or some combination?
Assuming your table is in A1:H6 and cell B10 = "Low" (you can hardcode "Low"
into the formula if you want), and B2:B5 - Ranks D2:H5 - Individual indicators for the colors. =SUM(--(FREQUENCY(IF(($B$2:$B$5=B10)*($D$2:$H$5<""),COLU MN($D$2:$H$5),""), IF(($B$2:$B$5=B10)*($D$2:$H$5<""),COLUMN($D$2:$H$ 5),""))0)) array entered with Cntrl+Shift+Enter (or you'll get #VALUE!). There may be a simpler way, but it's getting late for me. " wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT, SUM(IF(...), or some combination?
Works great! Thanks for your help. I've ended up adding a few more
booleans and it continues to function just fine. great solution! -Robert JMB wrote: Assuming your table is in A1:H6 and cell B10 = "Low" (you can hardcode "Low" into the formula if you want), and B2:B5 - Ranks D2:H5 - Individual indicators for the colors. =SUM(--(FREQUENCY(IF(($B$2:$B$5=B10)*($D$2:$H$5<""),COLU MN($D$2:$H$5),""), IF(($B$2:$B$5=B10)*($D$2:$H$5<""),COLUMN($D$2:$H$ 5),""))0)) array entered with Cntrl+Shift+Enter (or you'll get #VALUE!). There may be a simpler way, but it's getting late for me. " wrote: 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? |
Reply |
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 |