How can I subtotal rows based on 2 criteria?

iampseudonymous

New Member
Joined
Mar 29, 2011
Messages
1
Hi,

I am trying to create a summary table that shows the total monthly revenue achieved by resource as shown in the mock up (image 1). The tricky part is that I need to use 2 criteria to return the correct total and the data that is being summed sits in rows not columns. In effect I want to write a formula that looks at the name (column B) and month (row 3) in the summary sheet (image 1) and then returns the subtotal for all the days billed by the corresponding person (image 2 - column B) in that month (image 2 - Row 2).

The issue I am coming up against is that the monthly subtotal needs to sum the daily totals that are presented in multiple columns (gantt style) and that means the summing needs to be done in rows. I’ve tried using the conditional sum wizard (wants the data being summed in a single column not row), I’ve tried Sumifs (needs the range criteria to be identical and they’re not) and I’ve tried Sumproduct (not working and I suspect not flexible enough). I’m sure the answer lies in some form of summed Hlookup CSE type formula but currently I’m having no success. Any help that can be offered would be appreciated.

Image 1
Untitled1.jpg


Image 2
Untitled.jpg
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
I can't see those images ... I don't know about anyone else. That makes it difficult to understand what you are on about.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,082
Messages
5,622,581
Members
415,909
Latest member
vbaBeginner94

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
Top