I've got a worksheet that contains several hundred rows (similar to what is in rows A10:E10) on the attached spreadsheet.
What I'm looking for is a function (I think it is a combination of SumIf() and And() ) which I can use in all of the cells (same function in each cell of the range (H18:J20)) which will sum all of the values in the A10:E10 which match the values in the associated row and column headers. Something like:
H18 = SumIf(B2:B10 = G18 and C1:E1 = H1, C2:E10)
Hope this makes sense.
I could write a function to do this, but before I go there, I thought I'd use check to see if some inventive expert could wrap this up in an already existing Excel function.
SumIf.xlsx
Add labels to the first two columns
select the data
Insert > Pivot table < ok
In the choose fields pane
drag the title of the second column to the "Row Labels" box
drag each of the titles of the dates column to the "Sigma values" box