SUMPRODUCT with 2 criterias
 

I created this formula, but it is not working.
=SUMPRODUCT(--($AX$4:$AX$7312000)+--+($BA$4:$BA$73125),
--($BA$4:$BA$73<200))*($AX$4:$AX$73)
The column AX has dollar values and Column BA has number of occurrence.
I want to measure if the occurrence is between the range of 125-200 or if
the dollar value is greater than $ 12,000 but less than $ 24,000. I did not
want the conditions as "AND", which is why I am using + sign. Even though I
did not write the second condition of <$ 24,000, I am hoping some experts
will help me achieve my objective.

Thanks

SUMPRODUCT with 2 criterias
 

Here you can learn how to apply SUMPRODUCT

If you don't use AND you can't get 125 AND <200


--


Regards,


Peo Sjoblom


"Krish" wrote in message
...
I created this formula, but it is not working.
=SUMPRODUCT(--($AX$4:$AX$7312000)+--+($BA$4:$BA$73125),
--($BA$4:$BA$73<200))*($AX$4:$AX$73)
The column AX has dollar values and Column BA has number of occurrence.
I want to measure if the occurrence is between the range of 125-200 or if
the dollar value is greater than $ 12,000 but less than $ 24,000. I did
not
want the conditions as "AND", which is why I am using + sign. Even though
I
did not write the second condition of <$ 24,000, I am hoping some experts
will help me achieve my objective.

Thanks

SUMPRODUCT with 2 criterias
 

You want the A-values to be greater than 12000, AND the B values to be 125
AND less than 200 so you do want AND
=SUMPRODUCT(--($AX$4:$AX$7312000), --($BA$4:$BA$73125),
--($BA$4:$BA$73<200), $AX$4:$AX$73)
For more on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Krish" wrote in message
...
I created this formula, but it is not working.
=SUMPRODUCT(--($AX$4:$AX$7312000)+--+($BA$4:$BA$73125),
--($BA$4:$BA$73<200))*($AX$4:$AX$73)
The column AX has dollar values and Column BA has number of occurrence.
I want to measure if the occurrence is between the range of 125-200 or if
the dollar value is greater than $ 12,000 but less than $ 24,000. I did
not
want the conditions as "AND", which is why I am using + sign. Even though
I
did not write the second condition of <$ 24,000, I am hoping some experts
will help me achieve my objective.

Thanks

SUMPRODUCT with 2 criterias
 

"Bernard Liengme" wrote:

You want the A-values to be greater than 12000, AND the B values to be 125
AND less than 200 so you do want AND
=SUMPRODUCT(--($AX$4:$AX$7312000), --($BA$4:$BA$73125),
--($BA$4:$BA$73<200), $AX$4:$AX$73)

May be I did not express clearly in posing the question. I did not want the AND, because I want to get the results either if $BA125 and $BA<200 OR if $AX12000 and $AX<24000.

SUMPRODUCT with 2 criterias
 

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Regardless what you say you would still need AND (that's what the * does)

=SUMPRODUCT((A2:A2012000)*(A2:A20<24000)+(B2:B20 125)*(B2:B20<200))

will check those 2 ranges



--


Regards,


Peo Sjoblom






"Krish" wrote in message
...

"Bernard Liengme" wrote:

You want the A-values to be greater than 12000, AND the B values to be
125
AND less than 200 so you do want AND
=SUMPRODUCT(--($AX$4:$AX$7312000), --($BA$4:$BA$73125),
--($BA$4:$BA$73<200), $AX$4:$AX$73)

May be I did not express clearly in posing the question. I did not want
the AND, because I want to get the results either if $BA125 and $BA<200
OR if $AX12000 and $AX<24000.