SUMIFS Formula with 2 criteria over multiple columns

[TallAussie]

Hi there,

This one stumped me - I attempted a few of the solutions I found on this forum (e.g. using SUMPRODUCT, Arrays, etc.) but I couldn't get any to work. Are you able to assist?

• Data is downloaded looking like below. One issue is it only shows the days so far and always has a total column at the end (which is no use for this exercise). Also, if there is no data for a particular date then that wont show up.

• I have added column A to act as the "lookup value" which combines the data from columns C and D (D is picked up from the last completed value as the software tends to stop its self from putting the same heading twice)

• The following sheet is where I need you help coming up with the formula. I need to be able to pick up the sum based on the criteria in column A and row C. For example, cell C3 should be the total number of patients in "DPU - Day Only Patients" for the 1st of June. Manually we can calculate this being 4. I will then need to copy this formula across for all dates and down for all wards.

Thanks in advance for any help you might be able to give!

Cheers

 

[TallAussie]

FYI I was able to resolve this with the support of others. Formula used is in cell C3;

=IFERROR(SUMIFS(INDEX('raw data sheet'!$F$7:$S$999,,MATCH(C$2,'raw data sheet'!$F$3:$BA$3,0)),'raw data sheet'!$A$7:$A$999,$A3),0)