Sumifs with vertical and horizontal criteria

P

pttoomey

New Member
Joined
Oct 18, 2014
Messages
2
Excel guru's,

I am trying to do an array formula similar to sumifs() but I have horizontal and vertical criteria. The formula I know and LOVE is the sum-if array [{=sum(if((.....)*(.....),(......)))}] but this only works if the array range is exactly the same vertically or horizontally, i.e. A2:A50 or Q2:Q50 or P2:P50.

The problem I am having is I need to sum the number of hours employees have worked in a week in a report I am creating, however the issue I have is the employee list in the summary sheet may change from time to time due to employees coming or going. See below a basic set-up similar to what I am running:

[TABLE="width: 647"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: center"]John Smith[/TD]
[TD="align: center"]Jane Smith[/TD]
[TD="align: center"]Peter Johnson[/TD]
[TD="align: center"]Jo Blow[/TD]
[TD="align: center"]Peter Bloke[/TD]
[TD="align: center"]Jane Thompson[/TD]
[TD="align: center"]Jake Brake[/TD]
[/TR]
[TR]
[TD="align: center"]1/10/2014[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]2/10/2014[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3/10/2014[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]4/10/2014[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]5/10/2014[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD="align: center"]6/10/2014[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD="align: center"]7/10/2014[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]8/10/2014[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD="align: center"]9/10/2014[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]10/10/2014[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]11/10/2014[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD="align: center"]12/10/2014[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]13/10/2014[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD="align: center"]14/10/2014[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"]Weekly Start[/TD]
[TD="align: center"]4/10/2014[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"]Week End[/TD]
[TD="align: center"]10/10/2014[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"]Hours Worked[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD]48[/TD]
[TD="colspan: 6"]{=SUM(IF(($B$3:$B$16>=$D$19)*($B$3:$B$16<=$D$20),(D$3:D$16)))}[/TD]
[/TR]
[TR]
[TD]Jo Blow[/TD]
[TD]12[/TD]
[TD="colspan: 6"]{=SUM(IF(($B$3:$B$16>=$D$19)*($B$3:$B$16<=$D$20),(E$3:E$16)))}[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]48[/TD]
[TD="colspan: 6"]{=SUM(IF(($B$3:$B$16>=$D$19)*($B$3:$B$16<=$D$20),(C$3:C$16)))}[/TD]
[/TR]
[TR]
[TD]Peter Bloke[/TD]
[TD]60[/TD]
[TD="colspan: 6"]{=SUM(IF(($B$3:$B$16>=$D$19)*($B$3:$B$16<=$D$20),(F$3:F$16)))}[/TD]
[/TR]
</tbody>[/TABLE]



Now the problem is, the employee list to report may change (based on no criteria at all) to (for example):
[TABLE="width: 96"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Jane Thompson[/TD]
[/TR]
[TR]
[TD]Jo Blow[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[/TR]
[TR]
[TD]Peter Bloke[/TD]
[/TR]
[TR]
[TD]Peter Johnson

or next week

[TABLE="width: 96"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Jake Brake[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[/TR]
[TR]
[TD]Jane Thompson[/TD]
[/TR]
[TR]
[TD]Peter Bloke[/TD]
[/TR]
[TR]
[TD]Peter Johnson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Suggestions would be much appreciated.

regards.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
I think your example has an error for Jo Blow

Here is one solution, copy down to the end of the list of names
Excel Workbook
ABCDEFGH
1John SmithJane SmithPeter JohnsonJo BlowPeter BlokeJane ThompsonJake Brake
21/10/201412001212120
32/10/20141212001200
43/10/20140120120120
54/10/201401201212120
65/10/2014120012121212
76/10/20141200120012
87/10/20141212000120
98/10/2014001212121212
109/10/20140120121204
1110/10/20141212001200
1211/10/2014120012121212
1312/10/20140121212000
1413/10/201412120012012
1514/10/20141212000120
16
17From4/10/2014
18To10/10/2014
19
20Jane Thompson48
21Jo Blow60
22John Smith48
23Peter Bloke60
24Peter Johnson12
Sheet1
 
Upvote 0
If you are on a post-2003 Excel system...

B20, just enter and copy down:
Rich (BB code): 
=SUMIFS(INDEX($B$2:$H$15,0,MATCH($A20,$B$1:$H$1,0)),
  $A$2:$A$15,">="&$B$17,$A$2:$A$15,"<="&$B$18)
 
Upvote 0
You must log in or register to reply here.

Similar threads

montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
100
montecarlo2012
montecarlo2012
D
  • Question Question
Scoring weighted permutations
Replies
1
Views
219
StephenCrump
S
J
  • Question Question
Stacked Clustered Column Chart
Replies
0
Views
262
johnnypp
J
ExcelNewbie2020
  • Solved
Convert horizontal data to vertical with running balance
Replies
4
Views
200
alansidman
A
S
  • Question Question
Formula for Equity Vesting
Replies
6
Views
1K
Dave Patton
D

Forum statistics

Threads
1,223,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

Share this page

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