Summing values based on two criteria

Hi smartguy. Try:

=SUMPRODUCT(--(Property="School")*(Bin_Type="Euro")*(No.Of_Bins))

Hi Jason,

I've tried your formulae, but all I get as a result is '0', any ideas?

Smartguy

That is odd. The formula should work as you requested. Can you post up a copy of your workbook so I can take a look at it?

Originally Posted by smartguy

Hi all,

I have a problem that has been driving me mad, can someone help?

I have 3 named ranges (Property, Bin_Type & No.Of_Bins). What I need Excel to do is search the Property range for a specific type (ie School), then search Bin_Type for a specific type (ie Euro), then to total the value associated in the No.Of_Bins range.

An example below:

Property Bin_Type No.Of_Bins
School Euro 4
Council 1100 2
School Euro 1



What I need in another column is the total number of Euros at Schools (ie 5).

The example is very basic, the spreadie is approx 2000 rows, with various combinations of properties, bin types & bin numbers but if I can get this simple one done then I will know what to do.

What I cannot do is alter the spreadie in any way as it is used for other things.

I hope I have explained this clearly!

Thanks in advance.

Smartguy

=SUMPRODUCT(--(A1:A2000="School"),--(B1:B2000="Euro"),--(C1:C2000=5))

Hi Jason,

Here's the file, the columns I want to use are F, H & I.

Smartguy

Smartguy, the problem stems from the fact that the Bin_Type range does not contain any cells with "Euro". What exactly are you looking for in this range?

Hi Jason,

Firstly appologies, I forgot I had taken Euro out of the spreadie!!! (i've been at this too long!).

Let me try to explain again,

What I need to do is provide a summary of the totals of bin types at all the different property types (ie at Schools there are so many 1100, so many 660).

So for a given property type I need a total amount of bins broken down into the different types.

Does this help?.

Not so Smartguy!

Yes, it does. You have a couple options. You could either use various Sumproduct functions, or you could create a pivot table. See attached.

Hi Jasoncw,

That is brilliant!!!, its exactly what I needed!, I will use the pivot table as it shows all the info better.

I can finally get this finished, as it has been driving me mad!!.

Sorry I didn't explain things better!

Thanks ever so much

Smartguy

No problem. Glad I could help.