SUMIFS - Multiple columns at the same time?

[StephenM123]

Afternoon all,

Looking for some help/advice please... I hope this makes sense.

I have a holiday file which is set out in a similar manner as below and tracks the full year.
The actual file has around 400 employees in it but for the sake of this I've just classes a few as different job types.
The columns do not change and stay the same but I want my output to have an 8 week future view of hours booked.

I'm looking for a way to sum all holidays hours from the week per process in to seperate buckets. (I want to use a formula if possible and not a pivot)
I know I could do multiple sumifs for each column and add them together in the one but surely there is an easier way?

	Week Num	40	40	40	40	40	41	41	41	41	41	41	41	42	42	42	42	42	42	42
Job	Date	01-Oct	02-Oct	03-Oct	04-Oct	05-Oct	06-Oct	07-Oct	08-Oct	09-Oct	10-Oct	11-Oct	12-Oct	13-Oct	14-Oct	15-Oct	16-Oct	17-Oct	18-Oct	19-Oct
Desk	Day					10	10													10
Desk	Day	10
Desk	Day		10	10	10											10	10	10	10
Desk	Day
Desk	Back
Desk	Back
Logistics	Day	10	10	10	10	10	10	10	10	10	10
Logistics	Day
Logistics	Day
Logistics	Day											10	10	10	10	10			5	5
Logistics	Back			10	10
Logistics	Back	10	10
Operation	Day
Operation	Day								10
Operation	Day							10
Operation	Day							10
Operation	Back							10
Operation	Back							10
Virtual	Day							10
Virtual	Back
Virtual	Back

<tbody>
</tbody>

Example Output - This would give an 8 week view changing as the weeks tick by:

Job/Week	40	41	42
Desk	50	10	50
Logistics	90	70	40
Operation		50
Virtual		10

<tbody>
</tbody>

Any help would be brilliant or if you need more information I'll try give you as much as possible.

Thank you!

 

[RoryA]

Use SUMPRODUCT instead:

=SUMPRODUCT((first column = "Desk")*(firstrow = 40)*datarange)

adjusting the ranges to fit your data.

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		Week Num	40	40	40	40	40	41	41	41	41	41	41	41	42	42	42	42	42	42	42
2	Job	Date	01-Oct	02-Oct	03-Oct	04-Oct	05-Oct	06-Oct	07-Oct	08-Oct	09-Oct	10-Oct	11-Oct	12-Oct	13-Oct	14-Oct	15-Oct	16-Oct	17-Oct	18-Oct	19-Oct
3	Desk	Day					10	10													10
4	Desk	Day	10
5	Desk	Day		10	10	10											10	10	10	10
6	Desk	Day
7	Desk	Back
8	Desk	Back
9	Logistics	Day	10	10	10	10	10	10	10	10	10	10
10	Logistics	Day
11	Logistics	Day
12	Logistics	Day											10	10	10	10	10			5	5
13	Logistics	Back			10	10
14	Logistics	Back	10	10
15	Operation	Day
16	Operation	Day								10
17	Operation	Day							10
18	Operation	Day							10
19	Operation	Back							10
20	Operation	Back							10
21	Virtual	Day							10
22	Virtual	Back
23	Virtual	Back
24
25
26
27	Job/Week	40	41	42
28	Desk	50	10	50
29	Logistics	90	70	40
30	Operation	0	50	0
31	Virtual	0	10	0
Lookup

Cell Formulas
Range	Formula
B28	=SUMPRODUCT(($A$3:$A$23=$A28)*($C$1:$U$1=B$27),$C$3:$U$23)

 

[StephenM123]

Thank you!

Totally works. I'll use this method just now.

It seems to be a super intense way of doing this due to the number of people/full years worth of data.

Either of you don't know of a way that might not make my sheet slow down so much?

 

[Swayzy]

Is there a specific reason to not use a pivot table? There is a big reason why VBA and power pivot and power query has been developed, handling big data with normal Excel formulas are computation heavy.

 

[StephenM123]

My previous experience is that Pivots increase file size by a huge amount.

Maybe I've missed a trick and need to weigh up file size vs the workload.

 

[Fazza]

With the data sorted - column A & row 1 - like the example, maybe this would be OK?
Use MATCH functions to identify the first row/column, and COUNTIF for how many rows/columns. Then SUM(OFFSET(range you want))
Details below


Enter in cells
C36 "Desk"
C37 "Logistics"
C38 "Operation"
C39 "Virtual"


In row 33 enter
D33 40
E33 41
F33 42


In row 34
C34 "offset to first"
D34 =MATCH(D33,1:1,0)-1
drag D34 across to fill D34:F34 [See results 2,7,14]


In row 35
A35 "offset to first"
B35 "count"
C35 "count"
D35 =COUNTIF(1:1,D33)
drag D35 across to fill D35:F35 [See results 5,7,7]


Down column A
A36 =MATCH(C36,$A$1:$A$23,0)-1
drag A36 down to fill A36:A39 [See results 2,8,14,20]
B36 =COUNTIF($A$1:$A$23,C36)
drag B36 down to fill B36:B39 [See results 6,6,6,3]


in cell
D36 =SUM(OFFSET($A$1,$A36,D$34,$B36,D$35))
drag to fill D36:F39

 

[Swayzy]

For me File size is less an issue then a file that Always need to have calculations set to manual, since most user dont get that they then need to force a calculation to get updated values. With a pivot or vba I can just make a refresh button.

With power pivot you can even export your raw data into a separate text file and only have pivots in the workbook further reducing the size.