Will sumproduct work?

[Risky Dave]

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

 

[Ashish Mathur]

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