How can I subtotal rows based on 2 criteria?

[iampseudonymous]

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

Image 2

 

[GlennUK]

I can't see those images ... I don't know about anyone else. That makes it difficult to understand what you are on about.