Formula for searching 3 different Criteria

[Craig]

A1 B1 C1
Location Hours Month
Essex 6 November
Kent 13 November
Surrey 14 December
Middlesex 24 January
Essex 17 January
Kent 12 January
Essex 16 January
Essex 21 January

From the table above I need a formula that will filter out the location i
want, total the number of hours for that location but only in a given month.

For example in the table above i would be looking for a total number of
hours spent in Essex but only those for January. The answer would therefore
read 54. Answer would be in D1.

Thanks in advance
Craig

 

[Roger Govier]

Hi Craig

=SUMPRODUCT(($A$2:$A$100="Essex")*($C$2:$C$100="November")*$B$2:$B$100)
Better to put the variables in cells
Put Months in F1 going across. Put Counties in E2 going down
=SUMPRODUCT(($A$2:$A$100=$E2)*($C$2:$C$100=$F1)*$B$2:$B$100)
Copy across and down

Better still use a Pivot Table
Place cursor in table>Data>Pivot Table>Finish
On the PT skeleton that appears on a ne sheet
Drag Location from the field list to the Row Area
Drag Month from the field list to the Column Area
Drag Hours to the Data area