Formula for searching 3 different Criteria
Hi Craig
=SUMPRODUCT(($A$2:$A$100="Essex")*($C$2:$C$100="No vember")*$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 tableDataPivot TableFinish
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
--
Regards
Roger Govier
"Craig" wrote in message
...
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
|