SUMIFS - Multiple columns at the same time?

StephenM123

New Member
Joined
Apr 19, 2019
Messages
8
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?


[TABLE="class: grid, width: 1349, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]Week Num
[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]41[/TD]
[TD]41[/TD]
[TD]41[/TD]
[TD]41[/TD]
[TD]41[/TD]
[TD]41[/TD]
[TD]41[/TD]
[TD]42[/TD]
[TD]42[/TD]
[TD]42[/TD]
[TD]42[/TD]
[TD]42[/TD]
[TD]42[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]Job
[/TD]
[TD]Date[/TD]
[TD]01-Oct[/TD]
[TD]02-Oct[/TD]
[TD]03-Oct[/TD]
[TD]04-Oct[/TD]
[TD]05-Oct[/TD]
[TD]06-Oct[/TD]
[TD]07-Oct[/TD]
[TD]08-Oct[/TD]
[TD]09-Oct[/TD]
[TD]10-Oct[/TD]
[TD]11-Oct[/TD]
[TD]12-Oct[/TD]
[TD]13-Oct[/TD]
[TD]14-Oct[/TD]
[TD]15-Oct[/TD]
[TD]16-Oct[/TD]
[TD]17-Oct[/TD]
[TD]18-Oct[/TD]
[TD]19-Oct[/TD]
[/TR]
[TR]
[TD]Desk[/TD]
[TD]Day[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Desk[/TD]
[TD]Day[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Desk[/TD]
[TD]Day[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Desk[/TD]
[TD]Day[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Desk[/TD]
[TD]Back[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Desk[/TD]
[TD]Back[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Logistics[/TD]
[TD]Day[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Logistics[/TD]
[TD]Day[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Logistics[/TD]
[TD]Day[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Logistics[/TD]
[TD]Day[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Logistics[/TD]
[TD]Back[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Logistics[/TD]
[TD]Back[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Operation[/TD]
[TD]Day[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Operation[/TD]
[TD]Day[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Operation[/TD]
[TD]Day[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Operation[/TD]
[TD]Day[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Operation[/TD]
[TD]Back[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Operation[/TD]
[TD]Back[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Virtual[/TD]
[TD]Day[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Virtual[/TD]
[TD]Back[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Virtual[/TD]
[TD]Back[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="width: 64"]Job/Week
[/TD]
[TD="width: 69"]40
[/TD]
[TD="width: 64"]41
[/TD]
[TD="width: 64"]42
[/TD]
[/TR]
[TR]
[TD]Desk
[/TD]
[TD]50[/TD]
[TD]10[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Logistics[/TD]
[TD]90[/TD]
[TD]70[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]Operation
[/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Virtual
[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]











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

Thank you!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Use SUMPRODUCT instead:

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

adjusting the ranges to fit your data.
 
Upvote 0
How about


Book1
ABCDEFGHIJKLMNOPQRSTU
1Week Num40404040404141414141414142424242424242
2JobDate01-Oct02-Oct03-Oct04-Oct05-Oct06-Oct07-Oct08-Oct09-Oct10-Oct11-Oct12-Oct13-Oct14-Oct15-Oct16-Oct17-Oct18-Oct19-Oct
3DeskDay101010
4DeskDay10
5DeskDay10101010101010
6DeskDay
7DeskBack
8DeskBack
9LogisticsDay10101010101010101010
10LogisticsDay
11LogisticsDay
12LogisticsDay101010101055
13LogisticsBack1010
14LogisticsBack1010
15OperationDay
16OperationDay10
17OperationDay10
18OperationDay10
19OperationBack10
20OperationBack10
21VirtualDay10
22VirtualBack
23VirtualBack
24
25
26
27Job/Week404142
28Desk501050
29Logistics907040
30Operation0500
31Virtual0100
Lookup
Cell Formulas
RangeFormula
B28=SUMPRODUCT(($A$3:$A$23=$A28)*($C$1:$U$1=B$27),$C$3:$U$23)
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top