SUMIFS with multiple criteria in columns and rows

K

k_one

New Member
Joined
Mar 22, 2012
Messages
18
Hi all,


I'm attempting to consolidate a big block of data with two columns and a reference to a month into a summary table and I can't seem to get there.

Essentially I want to sum for example, all the items in the reference table that are CAPEX and Labour for the Month of September into the summary table in C2.

Instead of merging cells D1 and E1 previously I was using OFFSET and I had duplicated the month into both columns but it probably isn't the right way to go about it and I can't figure out how to get it to differentiate between Forecast and Actual because I wrote that formula over a year ago...

(To add a layer of complexity - is there also a way to choose the entire Actuals column over the Forecast column if there are Actuals loaded in the column? So for example I won't have Actuals in the November column yet because it's not November - but if I did can I sum that column instead? Knowing also that not every row is always accounted for each month as resources come and go. If not, I can remove the forecast after entering actuals nbd)

I've been working on this on and off for a week and I'm admitting defeat now! Any help would be more than much appreciated. :help:

Ref data table:

[TABLE="width: 547"]
<tbody>[TR]
[TD="width: 47, bgcolor: transparent"][/TD]
[TD="width: 125, bgcolor: transparent"][/TD]
[TD="width: 196, bgcolor: transparent"][/TD]
[TD="width: 118, bgcolor: transparent, colspan: 2"]Sep-18

[/TD]
[TD="width: 129, bgcolor: transparent, colspan: 2"]Oct-18
[/TD]
[TD="width: 113, bgcolor: transparent, colspan: 2"]Nov-18
[/TD]
[/TR]
[TR]
[TD="width: 47, bgcolor: transparent"]CAPEX / OPEX
[/TD]
[TD="width: 125, bgcolor: transparent"]Category
[/TD]
[TD="width: 196, bgcolor: transparent"]Vendor/Description
[/TD]
[TD="width: 49, bgcolor: transparent"]Forecast
[/TD]
[TD="width: 69, bgcolor: transparent"]Actual
[/TD]
[TD="width: 70, bgcolor: transparent"]Forecast
[/TD]
[TD="width: 59, bgcolor: transparent"]Actual
[/TD]
[TD="width: 70, bgcolor: transparent"]Forecast
[/TD]
[TD="width: 43, bgcolor: transparent"]Actual
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OPEX
[/TD]
[TD="bgcolor: transparent"]Travel
[/TD]
[TD="bgcolor: transparent"]Travel
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 1,162.00
[/TD]
[TD="bgcolor: transparent"] $ 1,200.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 2,400.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OPEX
[/TD]
[TD="bgcolor: transparent"]Training
[/TD]
[TD="bgcolor: transparent"]Training
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 10,000.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OPEX
[/TD]
[TD="bgcolor: transparent"]Prof. Services
[/TD]
[TD="bgcolor: transparent"]Professional Services (Marketing)
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 6,445.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 45,000.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OPEX
[/TD]
[TD="bgcolor: transparent"]Legal
[/TD]
[TD="bgcolor: transparent"]External Legal
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 1,646.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 5,000.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OPEX
[/TD]
[TD="bgcolor: transparent"]Resource
[/TD]
[TD="bgcolor: transparent"]Change
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 7,402.00
[/TD]
[TD="bgcolor: transparent"] $ 9,825.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 9,825.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CAPEX
[/TD]
[TD="bgcolor: transparent"]Labour
[/TD]
[TD="bgcolor: transparent"]Resource 1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 93,537.00
[/TD]
[TD="bgcolor: transparent"] $ 20,400.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 15,300.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CAPEX
[/TD]
[TD="bgcolor: transparent"]Labour
[/TD]
[TD="bgcolor: transparent"]Resource 2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 23,161.60
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 23,161.60
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CAPEX
[/TD]
[TD="bgcolor: transparent"]Labour
[/TD]
[TD="bgcolor: transparent"]Resource 3
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 5,000.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 3,000.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CAPEX
[/TD]
[TD="bgcolor: transparent"]Labour
[/TD]
[TD="bgcolor: transparent"]Resource 4
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 11,040.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 11,040.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CAPEX
[/TD]
[TD="bgcolor: transparent"]Labour
[/TD]
[TD="bgcolor: transparent"]Resource 5
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CAPEX
[/TD]
[TD="bgcolor: transparent"]Labour
[/TD]
[TD="bgcolor: transparent"]Resource 6
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CAPEX
[/TD]
[TD="bgcolor: transparent"]Labour
[/TD]
[TD="bgcolor: transparent"]Resource 7
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CAPEX
[/TD]
[TD="bgcolor: transparent"]Labour
[/TD]
[TD="bgcolor: transparent"]Resource 8
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 10,000.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CAPEX
[/TD]
[TD="bgcolor: transparent"]Labour
[/TD]
[TD="bgcolor: transparent"]Resource 9
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CAPEX
[/TD]
[TD="bgcolor: transparent"]Vendor
[/TD]
[TD="bgcolor: transparent"]Email issue
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CAPEX
[/TD]
[TD="bgcolor: transparent"]Vendor
[/TD]
[TD="bgcolor: transparent"]3rd party implementation
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] $ 11,000.00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]



Summary table:

[TABLE="width: 365"]
<tbody>[TR]
[TD="width: 47, bgcolor: transparent"]Type
[/TD]
[TD="width: 125, bgcolor: transparent"]Category
[/TD]
[TD="width: 196, bgcolor: transparent, align: right"]Sep-18
[/TD]
[TD="width: 49, bgcolor: transparent, align: right"]Oct-18
[/TD]
[TD="width: 69, bgcolor: transparent"]Nov-18
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CAPEX
[/TD]
[TD="bgcolor: transparent"]Labour
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CAPEX
[/TD]
[TD="bgcolor: transparent"]Vendor
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CAPEX
[/TD]
[TD="bgcolor: transparent"]Software
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CAPEX
[/TD]
[TD="bgcolor: transparent"]Hardware
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OPEX
[/TD]
[TD="bgcolor: transparent"]Resource
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OPEX
[/TD]
[TD="bgcolor: transparent"]Training
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OPEX
[/TD]
[TD="bgcolor: transparent"]Professional Services
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OPEX
[/TD]
[TD="bgcolor: transparent"]Legal
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OPEX
[/TD]
[TD="bgcolor: transparent"]Maintenance
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OPEX
[/TD]
[TD="bgcolor: transparent"]Travel
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OPEX
[/TD]
[TD="bgcolor: transparent"]Other
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Here is how you could do the SUMIFS based on the actual column for each month.

=SUMIFS(INDEX($D$2:$I$18,0,MATCH(1,INDEX(--($D$1:$I$1=C$24),0),0)+1),$A$2:$A$18,$A25,$B$2:$B$18,$B25)

Id suggest making a new column that will take care of the actual/forecast problem you have by using an IF statement. It will be far easier that trying to get one formula to do all.
 
Upvote 0
Heres another which will take care of the columns problems:

=SUMPRODUCT(($A$3:$A$18=$A25)*($B$3:$B$18=$B25)*($D$1:$I$1=C$24)*IF(OFFSET($D$3:$I$18,0,1),OFFSET($D$3:$I$18,0,1),$D$3:$I$18))
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Stuck with a SUMIF formula - Adding Column B based on Column A criteria.
Replies
2
Views
511
reberryjr
R
B
  • Question Question
Excel VBA to highlight a cell when cell value changes and then copying entire row to a new log worksheet
Replies
1
Views
1K
mumps
mumps
G
  • Question Question
SUMIF name and total hours are within specific date range
2 3
Replies
21
Views
2K
Glasgow Girl
G
J
  • Question Question
Sum based on multiple column headers and row
Replies
3
Views
665
Domenic
D
C
  • Solved
Sumif total accross columns using an achored reference
Replies
5
Views
624
NiMip
N

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

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