Hi Derrick!
One way if your table (including headings) is in A1:C12
=SUMPRODUCT(($A$2:$A$12="Pdt2")*($B$2:$B$12="Q2")*($C$2:$C$12))
Another using an array formula:
=SUM(($A$2:$A$12="Pdt2")*($B$2:$B$12="Q2")*($C$2:$C$12))
Entered by pressing and holding down Ctrl and Shift and then pressing
Enter.
When correctly entered appears as:
{=SUM(($A$2:$A$12="Pdt2")*($B$2:$B$12="Q2")*($C$2:$C$12))}
Third way using another array formula:
=SUM(IF($A$2:$A$12="Pdt2",1,0)*IF($B$2:$B$12="Q2",1,0)*($C$2:$C$12))
Entered by pressing and holding down Ctrl and Shift and then pressing
Enter.
When correctly entered appears as:
{=SUM(IF($A$2:$A$12="Pdt2",1,0)*IF($B$2:$B$12="Q2",1,0)*($C$2:$C$12))}
In the first two formulas, the structure (A2:A12="Pdt2") and (B2:B12 =
"Q2")
can be regarded as implicit IF functions. In the third formula, we use
explicit IF functions which expose the logic of the first two
formulas.
Only if A and B elements in a row are both true will they produce 1
and
multiply the contents of C. The SUMPRODUCT or SUM functions just add
up the
results.
Rather than use the text strings "Pdt2" and "Q2" you might use cell
references to cells into which this strings are put. Then the formula
will be much more flexible in terms of data returned without having to
edit it.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Sunday 27th July: Cuba (Revolution Day),
Djibouti (Independence Day), Hong Kong (Lu Pan Day), North Korea
(Victory Day), Puerto Rico (Barbosa Day), Russian Federation (Navy
Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
