| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formulas with multiple criteria
Can anyone help me write a formula which would produce the following result.
I have three colums, two have criteria and the third has values in the cells that need to be added if the criteria in the other two columns matches. The worksheet is very large containing over 42,000 date rows. Any assistance greatly appreciated |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formulas with multiple criteria
Hi,
You don't say what the values are, text or numbers so:- This for text =SUMPRODUCT((A1:A42000="A")*(B1:B42000="B")*(C1:C4 2000)) This for numbers =SUMPRODUCT((A1:A42000=1)*(B1:B42000=2)*(C1:C42000 )) Mike "Shazaxacpcc" wrote: Can anyone help me write a formula which would produce the following result. I have three colums, two have criteria and the third has values in the cells that need to be added if the criteria in the other two columns matches. The worksheet is very large containing over 42,000 date rows. Any assistance greatly appreciated |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formulas with multiple criteria
Thanks for the advice, The first criteria column is text, the second is
number and the column that I need to add up if the criteria is met is a currency value. Would this change the formula? "Mike H" wrote: Hi, You don't say what the values are, text or numbers so:- This for text =SUMPRODUCT((A1:A42000="A")*(B1:B42000="B")*(C1:C4 2000)) This for numbers =SUMPRODUCT((A1:A42000=1)*(B1:B42000=2)*(C1:C42000 )) Mike "Shazaxacpcc" wrote: Can anyone help me write a formula which would produce the following result. I have three colums, two have criteria and the third has values in the cells that need to be added if the criteria in the other two columns matches. The worksheet is very large containing over 42,000 date rows. Any assistance greatly appreciated |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formulas with multiple criteria
It then becomes a mix of the 2
=SUMPRODUCT((A1:A42000="MyText")*(B1:B42000=9999)* (C1:C42000)) Mike "Shazaxacpcc" wrote: Thanks for the advice, The first criteria column is text, the second is number and the column that I need to add up if the criteria is met is a currency value. Would this change the formula? "Mike H" wrote: Hi, You don't say what the values are, text or numbers so:- This for text =SUMPRODUCT((A1:A42000="A")*(B1:B42000="B")*(C1:C4 2000)) This for numbers =SUMPRODUCT((A1:A42000=1)*(B1:B42000=2)*(C1:C42000 )) Mike "Shazaxacpcc" wrote: Can anyone help me write a formula which would produce the following result. I have three colums, two have criteria and the third has values in the cells that need to be added if the criteria in the other two columns matches. The worksheet is very large containing over 42,000 date rows. Any assistance greatly appreciated |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formulas with multiple criteria
Try SUMPRODUCT:
=SUMPRODUCT(--(A2:A100="Criteria1"),--(B2:B100="Criteria2),C2:C100) HTH, Paul -- "Shazaxacpcc" wrote in message ... Can anyone help me write a formula which would produce the following result. I have three colums, two have criteria and the third has values in the cells that need to be added if the criteria in the other two columns matches. The worksheet is very large containing over 42,000 date rows. Any assistance greatly appreciated |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formulas with multiple criteria
Thanks Paul
I've keyed in the following formula and it's returning a nil value, am I missing something? =SUMPRODUCT(--(A2:A42655="FBS"),--(K2:K24655="<31"),F2:F42655) Thanks again Shaz "PCLIVE" wrote: Try SUMPRODUCT: =SUMPRODUCT(--(A2:A100="Criteria1"),--(B2:B100="Criteria2),C2:C100) HTH, Paul -- "Shazaxacpcc" wrote in message ... Can anyone help me write a formula which would produce the following result. I have three colums, two have criteria and the third has values in the cells that need to be added if the criteria in the other two columns matches. The worksheet is very large containing over 42,000 date rows. Any assistance greatly appreciated |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formulas with multiple criteria
Second criteria. Change the operator and remove the quotes.
=SUMPRODUCT(--(A2:A42655="FBS"),--(K2:K24655<31),F2:F42655) Does that help? -- "SUMSUE" wrote in message ... Thanks Paul I've keyed in the following formula and it's returning a nil value, am I missing something? =SUMPRODUCT(--(A2:A42655="FBS"),--(K2:K24655="<31"),F2:F42655) Thanks again Shaz "PCLIVE" wrote: Try SUMPRODUCT: =SUMPRODUCT(--(A2:A100="Criteria1"),--(B2:B100="Criteria2),C2:C100) HTH, Paul -- "Shazaxacpcc" wrote in message ... Can anyone help me write a formula which would produce the following result. I have three colums, two have criteria and the third has values in the cells that need to be added if the criteria in the other two columns matches. The worksheet is very large containing over 42,000 date rows. Any assistance greatly appreciated |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formulas with multiple criteria
Paul, you are a star!!. How would the formula change of I wanted to do more
than 30 but less than 61?? "PCLIVE" wrote: Second criteria. Change the operator and remove the quotes. =SUMPRODUCT(--(A2:A42655="FBS"),--(K2:K24655<31),F2:F42655) Does that help? -- "SUMSUE" wrote in message ... Thanks Paul I've keyed in the following formula and it's returning a nil value, am I missing something? =SUMPRODUCT(--(A2:A42655="FBS"),--(K2:K24655="<31"),F2:F42655) Thanks again Shaz "PCLIVE" wrote: Try SUMPRODUCT: =SUMPRODUCT(--(A2:A100="Criteria1"),--(B2:B100="Criteria2),C2:C100) HTH, Paul -- "Shazaxacpcc" wrote in message ... Can anyone help me write a formula which would produce the following result. I have three colums, two have criteria and the third has values in the cells that need to be added if the criteria in the other two columns matches. The worksheet is very large containing over 42,000 date rows. Any assistance greatly appreciated |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formulas with multiple criteria
Try this:
=SUMPRODUCT(--(A2:A42655="FBS"),--(K2:K2465531),--(K2:K24655<61),F2:F42655) Regards, Paul -- "Shazaxacpcc" wrote in message ... Paul, you are a star!!. How would the formula change of I wanted to do more than 30 but less than 61?? "PCLIVE" wrote: Second criteria. Change the operator and remove the quotes. =SUMPRODUCT(--(A2:A42655="FBS"),--(K2:K24655<31),F2:F42655) Does that help? -- "SUMSUE" wrote in message ... Thanks Paul I've keyed in the following formula and it's returning a nil value, am I missing something? =SUMPRODUCT(--(A2:A42655="FBS"),--(K2:K24655="<31"),F2:F42655) Thanks again Shaz "PCLIVE" wrote: Try SUMPRODUCT: =SUMPRODUCT(--(A2:A100="Criteria1"),--(B2:B100="Criteria2),C2:C100) HTH, Paul -- "Shazaxacpcc" wrote in message ... Can anyone help me write a formula which would produce the following result. I have three colums, two have criteria and the third has values in the cells that need to be added if the criteria in the other two columns matches. The worksheet is very large containing over 42,000 date rows. Any assistance greatly appreciated |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I put multiple criteria in LOOKUP formulas | Excel Worksheet Functions | |||
| match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
| Array Formulas with multiple criteria in the same row? | Excel Worksheet Functions | |||
| multiple criteria for formulas | Excel Discussion (Misc queries) | |||
| Creating Array formulas with multiple criteria | Excel Worksheet Functions | |||
Hybrid Mode
