Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will sumproduct work?
Hi,
I have a workbook with data held across several sheets. For example: Sheet 1 A ........ E 1 ID1 ........ apples/not apples 2 ID2 ........ apples/not apples Column E can be seen as a logical test for the value "apples" Sheet 2 A B 1 ID1 ID2 .......... 2 nnn.nn nnn.nn Row 2 is the cost of each ID item Sheet 3 This sheet has a table that currently uses a set of SUMPRODUCT formulae to count the number of cost values from sheet 2 that fall within defined ranges: 0,<7 6,<15 14,<23 22 How do I modify this to count only the "apples" that fall into each of the defined cost ranges. I will also want to do a similar count of the "not apples" that fall into each cost range. Currently, the apples/not apples test is known for all ID values to be tested, but not all of them have costs associated with them. Where no cost is linked to an ID, 0 (zero) is in Sheet 2 row 2. I will convert this to a VB solution at a later date, but for now only formulaic solutions, please. This is in Office '07 under Vista, if that makes a difference. TIA Dave |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will sumproduct work?
Hi,
Try this array formula (Ctrl+Shift+Enter). SUMPRODUCT((Sheet1!$C$4:$C$8=1)*(TRANSPOSE(Sheet2! B$7:F$7$C10))*TRANSPOSE((Sheet2!B$7:F$7<$D10))) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Risky Dave" wrote in message ... Hi, I have a workbook with data held across several sheets. For example: Sheet 1 A ........ E 1 ID1 ........ apples/not apples 2 ID2 ........ apples/not apples Column E can be seen as a logical test for the value "apples" Sheet 2 A B 1 ID1 ID2 .......... 2 nnn.nn nnn.nn Row 2 is the cost of each ID item Sheet 3 This sheet has a table that currently uses a set of SUMPRODUCT formulae to count the number of cost values from sheet 2 that fall within defined ranges: 0,<7 6,<15 14,<23 22 How do I modify this to count only the "apples" that fall into each of the defined cost ranges. I will also want to do a similar count of the "not apples" that fall into each cost range. Currently, the apples/not apples test is known for all ID values to be tested, but not all of them have costs associated with them. Where no cost is linked to an ID, 0 (zero) is in Sheet 2 row 2. I will convert this to a VB solution at a later date, but for now only formulaic solutions, please. This is in Office '07 under Vista, if that makes a difference. TIA Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT won't work on a row | Excel Worksheet Functions | |||
SUMPRODUCT formula doesn't work! | Excel Discussion (Misc queries) | |||
SUMPRODUCT vs. COUNTIF -- Why does one work? | Excel Worksheet Functions | |||
sumproduct doesn't work | Excel Worksheet Functions | |||
Will SUMPRODUCT work for this? | Excel Discussion (Misc queries) |