sumproduct based on criteria in other columns

[asdfgfg]

I have a sheet with the following:

A: State
B: Leads (every row has an create date)
C: Product (A, B, or C)
D: Purchased (0=No, 1=Yes)

On another sheet I'm trying to create a chart with

[TABLE="width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Region[/TD]
[TD]Leads[/TD]
[TD]Customers[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]New England[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]West[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]East[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]West[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'm trying to find the formula for X that counts the number of leads per product in New England.

So far, I've been able to find just the number of leads in New England

=SUMPRODUCT(('Sheet'!RANGE={"Connecticut","Maine","Massachusetts","New Hampshire","Rhode Island","Vermont"}))

but I cannot filter down to leads per product 1 to solve for X.

I also want to further filter down to those leads interested in New England who became customers (the "Y" formula)

Any help would be much appreciated. I've been searching and working on this for hours now.

 

[James006]

Hello,

Why don't you make your life much easier ... by inserting a Pivot Table ... :wink: