SUMPRODUCT / SUMIFS Formula

[excelbytes]

I need some help being pointed in the right direction to create a formula. I have a Summary worksheet, and 31 additional worksheets, one for each day of the month. On the Summary worksheet is range B26:B39 that contains the name of a delivery company. On each of the 31 worksheets are the following ranges: C14:C43 = name of the delivery company, I14:I43 contains qty ordered, J14:J43 contains qty delivered, K14:K43 = cost per unit to delivery.

I need to sum up each of the 31 worksheets (that I have the worksheet names in a list named "Sheets"), (I-J)*K for each individual delivery company. The delivery companies will vary and won't be in order. Also the cost per unit will very on each line item.

Thanks in advance for your help.

 

[C Moore]

Power Query would handle this easily after a little bit of setup. I don't think its that much more complicated to learn than SUMPRODUCT and has been very useful for me.

 

[excelbytes]

Unfortunately, in this case, Power Query is not an option. Do you know how best to structure the formula for this?

 

[excelbytes]

Actually, I went the route of Power Query and I think it's going to work just fine. Thanks for the suggestion.

 

[Dave Patton]

You could put all the data from the 31 sheets into one Data sheet.
You could then use Pivot table, Data Table, Filters etc.

The example below works, Column L is the extension of Qty * Rate

With Excel versions prior to 365, the Sum formula must be Array Entered CSE or use the Sumproduct.

3D Sumif.xlsm
	A	B	C	D
1			105	105
2	Sheets		55	55
3	1a
4	1b
5	1c
Summary2

Cell Formulas
Range	Formula
C1:C2	C1	=SUM(SUMIF(INDIRECT("'"&Sheets&"'!"&"c14:C43"),B26,INDIRECT("'"&Sheets&"'!"&"L14:L43")))
D1:D2	D1	=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"c14:C43"),B26,INDIRECT("'"&Sheets&"'!"&"L14:L43")))

Named Ranges
Name	Refers To	Cells
Sheets	=Summary2!$A$3:$A$5	C1:D2

 

[excelbytes]

As I mentioned previously, I ultimately went the route of Power Query which pulled all 31 tables into one Master table. I added some calculations to the table and from there is was easy. I actually used a formula in my project virtually the same as one that you suggested: =SUM(SUMIFS(INDIRECT("'"&Sheets&"'!I14:I43"),INDIRECT("'"&Sheets&"'!C14:C43"),$B26))

My initial challenge was to create a formula that would pull the client specific data from each of the 31 sheets including the calculation of (I-J)*K to see if that was possible. Still wondering if it is, but not necessary since going with the Power Query approach.