Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Too many criterias... | Excel Worksheet Functions | |||
If Criterias | Excel Discussion (Misc queries) | |||
Sum If using 2 criterias | Excel Discussion (Misc queries) | |||
Additional Sumproduct Criterias | Excel Worksheet Functions | |||
Sumif or Sumproduct 2 criterias not working | Excel Discussion (Misc queries) |