Sum if Critea 1 and critera 2 are met
I want to use the same criteria for getting
the Average of a column
What version of Excel are you using?
This will work in all versions of Excel.
Array entered**
=AVERAGE(IF((A2:A100="Q4")*(C2:C100="prodA"),F2:F1 00))
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
--
Biff
Microsoft Excel MVP
"Nelson" wrote in message
...
Thanks that help, now if I want to use the same criteria for getting the
Average of a column that has percentages would I change sum product to
Average?
Thanks
--
Nelson
"Gary''s Student" wrote:
Any time you have multiple criteria, use SUMPRODUCT():
=SUMPRODUCT(--(A2:A100="Q4")*(C2:C100="prodA")*(F2:F100))
--
Gary''s Student - gsnu200859
"Nelson" wrote:
Good day,
I have a column A2:A100 that specifies Q1,2,3,4
I have a column C2:C100 that specifies Product (either A or B)
in column F2:F100 I have price paid
Now I want to create a query that says if A2:A100 = Q1 and C2:C100 =
product
A then add up all the matching prices from F2:F100
I was using this but I think I am missing something
=SUM(IF(((A2:A100)="Q4")*(MATCH(C2:C100,"prodA",0) ),F2::F100))
Any suggestions?
Thanks in advance
--
Nelson
|