Sum if Critea 1 and critera 2 are met
 

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

Sum if Critea 1 and critera 2 are met
 

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

Sum if Critea 1 and critera 2 are met
 

And of course you can omit the double unary minus as you've used * to
multiply.

=SUMPRODUCT((A2:A100="Q4")*(C2:C100="prodA")*(F2:F 100)) or
=SUMPRODUCT(--(A2:A100="Q4"),--(C2:C100="prodA"),(F2:F100))
--
David Biddulph

"Gary''s Student" wrote in message
...
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

Sum if Critea 1 and critera 2 are met
 

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

Sum if Critea 1 and critera 2 are met
 

Hi,

In 2007 you would use

=SUMIFS(F2:F100,A2:A100,"Q4",C2:C100,"prodA")

and

=AVERAGEIFS(F2:F100,A2:A100,"Q4",C2:C100,"prodA")

In 2003:

=AVERAGE(IF((A2:A100="Q4")*(C2:C100="prodA"),F2:F1 00,""))

This formula must be array entered - press Shift+Ctrl+Enter, instead of enter
or

=SUMPRODUCT(--(A2:A100="Q4"),--(C2:C100="prodA"),F2:F100)/=SUMPRODUCT(--(A2:A100="Q4"),--(C2:C100="prodA"))




--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"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

Sum if Critea 1 and critera 2 are met
 

In Fun:

And of course you can ommit the parentheses in the final argument of the
second formula when you use the double minus:

=SUMPRODUCT(--(A2:A100="Q4"),--(C2:C100="prodA"),(F2:F100))

=SUMPRODUCT(--(A2:A100="Q4"),--(C2:C100="prodA"),F2:F100)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"David Biddulph" wrote:

And of course you can omit the double unary minus as you've used * to
multiply.

=SUMPRODUCT((A2:A100="Q4")*(C2:C100="prodA")*(F2:F 100)) or
=SUMPRODUCT(--(A2:A100="Q4"),--(C2:C100="prodA"),(F2:F100))
--
David Biddulph

"Gary''s Student" wrote in message
...
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

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