| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criterion in a SUMIF function
How would I incorporate 2 criterion into the SUMIF function? For example, in
the following example I have the formula look to the A2:A2000 range for the value referrenced in C2 and sum only those numbers in range B2:B2000 that meet this criteria. That's the easy part. How would I add another layer of criteria to the formula below in addition to that specified by "C2" - say, EXACT(E2,VLOOKUP(E1,A2:B2000,2,FALSE))=TRUE? =SUMIF(B2:B2000,E2,C2:C2000) thx. -- iperlovsky |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Criterion in a SUMIF function
=SUMPRODUCT(--((A2:A200=C2)+(A2:A200=E2))*B2:B200)
"IPerlovsky" wrote: How would I incorporate 2 criterion into the SUMIF function? For example, in the following example I have the formula look to the A2:A2000 range for the value referrenced in C2 and sum only those numbers in range B2:B2000 that meet this criteria. That's the easy part. How would I add another layer of criteria to the formula below in addition to that specified by "C2" - say, EXACT(E2,VLOOKUP(E1,A2:B2000,2,FALSE))=TRUE? =SUMIF(B2:B2000,E2,C2:C2000) thx. -- iperlovsky |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Criterion in a SUMIF function
=SUMPRODUCT(--(B2:B2000=E2),--(EXACT(E2:E2000,VLOOKUP(E1,A2:B2000,2,FALSE))),C2: C2000)
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "IPerlovsky" wrote in message ... How would I incorporate 2 criterion into the SUMIF function? For example, in the following example I have the formula look to the A2:A2000 range for the value referrenced in C2 and sum only those numbers in range B2:B2000 that meet this criteria. That's the easy part. How would I add another layer of criteria to the formula below in addition to that specified by "C2" - say, EXACT(E2,VLOOKUP(E1,A2:B2000,2,FALSE))=TRUE? =SUMIF(B2:B2000,E2,C2:C2000) thx. -- iperlovsky |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Criterion in a SUMIF function
Is this formula not multiplying? I am trying only to sum a data set that
meets 2 conditions. -- iperlovsky "Teethless mama" wrote: =SUMPRODUCT(--((A2:A200=C2)+(A2:A200=E2))*B2:B200) "IPerlovsky" wrote: How would I incorporate 2 criterion into the SUMIF function? For example, in the following example I have the formula look to the A2:A2000 range for the value referrenced in C2 and sum only those numbers in range B2:B2000 that meet this criteria. That's the easy part. How would I add another layer of criteria to the formula below in addition to that specified by "C2" - say, EXACT(E2,VLOOKUP(E1,A2:B2000,2,FALSE))=TRUE? =SUMIF(B2:B2000,E2,C2:C2000) thx. -- iperlovsky |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Criterion in a SUMIF function
Thank you. I modified it slightly for simplicity as:
=SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9),M2:M2000) How would I employ that methodoligy in the following 2 formulas so that it would: countif, based on 2 criterion... =COUNTIF(--((J2:J2000=E8),--(K2:K2000=E9)),E8) and max(if, based on 2 criterion... =MAX(IF(--((J2:J2000=E8),--(K2:K2000=E9)),M2:M2000))) thx.. -- iperlovsky "Bob Phillips" wrote: =SUMPRODUCT(--(B2:B2000=E2),--(EXACT(E2:E2000,VLOOKUP(E1,A2:B2000,2,FALSE))),C2: C2000) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "IPerlovsky" wrote in message ... How would I incorporate 2 criterion into the SUMIF function? For example, in the following example I have the formula look to the A2:A2000 range for the value referrenced in C2 and sum only those numbers in range B2:B2000 that meet this criteria. That's the easy part. How would I add another layer of criteria to the formula below in addition to that specified by "C2" - say, EXACT(E2,VLOOKUP(E1,A2:B2000,2,FALSE))=TRUE? =SUMIF(B2:B2000,E2,C2:C2000) thx. -- iperlovsky |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Criterion in a SUMIF function
"IPerlovsky" wrote in message ... Thank you. I modified it slightly for simplicity as: =SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9),M2:M2000) How would I employ that methodoligy in the following 2 formulas so that it would: countif, based on 2 criterion... =COUNTIF(--((J2:J2000=E8),--(K2:K2000=E9)),E8) =SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9)) and max(if, based on 2 criterion... =MAX(IF(--((J2:J2000=E8),--(K2:K2000=E9)),M2:M2000))) Exactly that, but as an array formula, so commit with Ctrl-Shift-Enter, not just Enter. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Criterion in a SUMIF function
The reason I ask about the other 2 formulas is because I am receiving "the
formula you typed contains an error" when I try to execute them. Any idea why? -- iperlovsky "Bob Phillips" wrote: "IPerlovsky" wrote in message ... Thank you. I modified it slightly for simplicity as: =SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9),M2:M2000) How would I employ that methodoligy in the following 2 formulas so that it would: countif, based on 2 criterion... =COUNTIF(--((J2:J2000=E8),--(K2:K2000=E9)),E8) =SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9)) and max(if, based on 2 criterion... =MAX(IF(--((J2:J2000=E8),--(K2:K2000=E9)),M2:M2000))) Exactly that, but as an array formula, so commit with Ctrl-Shift-Enter, not just Enter. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| multiple criterion to give sum | Excel Worksheet Functions | |||
| VLOOKUP and DGET to find a value with multiple criterion | Excel Worksheet Functions | |||
| Counting text across multiple sheets with a specific criterion | Excel Worksheet Functions | |||
| Multiple criterion including wildcards to sum in a range | Excel Worksheet Functions | |||
| How do I put more than one criterion in a SUMIF function? | Excel Worksheet Functions | |||
Linear Mode
