SUMPRODUCT with Multiple Criteria?

[thbutterflycollector_]

Hi,

I am trying to sum the quantity of workers and the total duration of the shifts for an activity by activity and type of resource. However I keep getting the #SPILL error.

The source data is from a data dump a named range in a table

Essentially =SUMPRODUCT(Cost_Head="Staff")*(Sub_Head="Carpenters")*(Quantity,Duration)

The named ranges are on a different tab and I've checked that the number of rows reconciles which it does.

Any help appreicated.

 

[Fluff]

You are missing some brackets, try

=SUMPRODUCT((Cost_Head="Staff")*(Sub_Head="Carpenters")*(Quantity,Duration))

 

[thbutterflycollector_]

No joy unfortunately. I now get a #VALUE! error. Having checked the Quantities and Durations ranges there is no text in there. However there is text in these ranges for other categories who are not staff - but they are not the subject of this formula hence me thinking I do not need a sumif.

 

[Fluff]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[thbutterflycollector_]

Please see below:

Book1
	B	C	D	E	F	G	H	I	J	K	L
1	Cost_Head	Sub_Head	Quantity	Duration
2	Staff	Carpenters	6	10					Staff	Carpenters	#VALUE!
3	Staff	Carpenters	4	5					Staff	Plumbers	#VALUE!
4	Staff	Plumbers	10	12					Staff	Painters	#VALUE!
5	Staff	Plumbers	25	15					Staff	Floor Layers	#VALUE!
6	Staff	Painters	35	20
7	Staff	Floor Layers	4	25
8	Staff	Floor Layers	2	5
Sheet1

Cell Formulas
Range	Formula
L2:L5	L2	=SUMPRODUCT((Cost_Head=J2)*(Sub_Head=K2)*(Quantity,Duration))

Named Ranges
Name	Refers To	Cells
Cost_Head	=Table1[Cost_Head]	L2:L5
Duration	=Table1[Duration]	L2:L5
Quantity	=Table1[Quantity]	L2:L5
Sub_Head	=Table1[Sub_Head]	L2:L5

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	B	C	D	E	F	G	H	I	J	K	L
1	Cost_Head	Sub_Head	Quantity	Duration
2	Staff	Carpenters	6	10					Staff	Carpenters	80
3	Staff	Carpenters	4	5					Staff	Plumbers	495
4	Staff	Plumbers	10	12					Staff	Painters	700
5	Staff	Plumbers	25	15					Staff	Floor Layers	110
6	Staff	Painters	35	20
7	Staff	Floor Layers	4	25
8	Staff	Floor Layers	2	5
Data

Cell Formulas
Range	Formula
L2:L5	L2	=SUMPRODUCT((Table1[Cost_Head]=J2)*(Table1[Sub_Head]=K2)*Table1[Quantity],Table1[Duration])

 

[thbutterflycollector_]

Fantastic. Thank you. I did not realise that I had to include the Table Reference at the start - I thought by naming the ranges it would overcome this.

Thanks again.

 

[Fluff]

You can use named ranges, but I just used the structured references as I couldn't be bothered to setup the named ranges.

 

[thbutterflycollector_]

You can use named ranges, but I just used the structured references as I couldn't be bothered to setup the named ranges.

Just using this formula in the secure desktop I am working on (can't paste directly unfortunately) - the source table is derived from a Power Query collation of a number of tabs. Are there any properties of these types of tables that would prevent this formula working? In the "offline" version it is fine but I get a #VALUE! error in the secure version.

 

[Fluff]

Make sure that PQ is bringing in numbers rather than text.