Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help with Sumproduct, adding additional criteria
=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other"))
I'd like to have this same number returned, but I'd like it to filter out when =Data!M1:M2500 does NOT equal "FA" or "Nonrecordable". Can anyone help me with this? Conversely, I'd like the same calculation in another cell =SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")) that adds exclusively counts when the values of =Data!M1:M2500 values are "FA" or Recordable" Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help with Sumproduct, adding additional criteria
try
=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")*(Data !M$1:M$2514<{"FA", "Nonrecordable"})) change < to = for the second part of your question. "Gina" wrote: =SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")) I'd like to have this same number returned, but I'd like it to filter out when =Data!M1:M2500 does NOT equal "FA" or "Nonrecordable". Can anyone help me with this? Conversely, I'd like the same calculation in another cell =SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")) that adds exclusively counts when the values of =Data!M1:M2500 values are "FA" or Recordable" Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help with Sumproduct, adding additional criteria
Hi
Try this: =SUMPRODUCT(--(Data!E$1:E$2514=Lists!A13),--(Data!E$1:E$2514<Lists!B13),--(Data!K$1:K$2514="Other"),(AND(Data!$M$1:$M$2514< "FA")*(Data!$M$1:$M$2514<"Nonrecordable"))) and =SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other"))-=SUMPRODUCT(--(Data!E$1:E$2514=Lists!A13),--(Data!E$1:E$2514<Lists!B13),--(Data!K$1:K$2514="Other"),(AND(Data!$M$1:$M$2514< "FA")*(Data!$M$1:$M$2514<"Nonrecordable"))) Regards, Per "Gina" skrev i meddelelsen ... =SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")) I'd like to have this same number returned, but I'd like it to filter out when =Data!M1:M2500 does NOT equal "FA" or "Nonrecordable". Can anyone help me with this? Conversely, I'd like the same calculation in another cell =SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")) that adds exclusively counts when the values of =Data!M1:M2500 values are "FA" or Recordable" Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help with Sumproduct, adding additional criteria
Sorry - my previous suggestion will not work for the < condition.
=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")*(Data !M$1:M$2514<"FA")*(Data!M$1:M$2514<"Nonrecordabl e")) "JMB" wrote: try =SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")*(Data !M$1:M$2514<{"FA", "Nonrecordable"})) change < to = for the second part of your question. "Gina" wrote: =SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")) I'd like to have this same number returned, but I'd like it to filter out when =Data!M1:M2500 does NOT equal "FA" or "Nonrecordable". Can anyone help me with this? Conversely, I'd like the same calculation in another cell =SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")) that adds exclusively counts when the values of =Data!M1:M2500 values are "FA" or Recordable" Thank you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help with Sumproduct, adding additional criteria
=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")*(ISNA (MATCH(Data!M$1:M$2514,{"FA","Nonrecordable"},))))
"Gina" wrote: =SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")) I'd like to have this same number returned, but I'd like it to filter out when =Data!M1:M2500 does NOT equal "FA" or "Nonrecordable". Can anyone help me with this? Conversely, I'd like the same calculation in another cell =SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")) that adds exclusively counts when the values of =Data!M1:M2500 values are "FA" or Recordable" Thank you. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help with Sumproduct, adding additional criteria
Thank you guys so much. It's always nice to know that when I get to the
point of banging my head into the wall, I can find help here. (You are the ones who taught me how to use sumproduct in the first place!) Gina "Teethless mama" wrote: =SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")*(ISNA (MATCH(Data!M$1:M$2514,{"FA","Nonrecordable"},)))) "Gina" wrote: =SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")) I'd like to have this same number returned, but I'd like it to filter out when =Data!M1:M2500 does NOT equal "FA" or "Nonrecordable". Can anyone help me with this? Conversely, I'd like the same calculation in another cell =SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")) that adds exclusively counts when the values of =Data!M1:M2500 values are "FA" or Recordable" Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If - additional criteria | Excel Discussion (Misc queries) | |||
Subtotal with additional criteria | Excel Worksheet Functions | |||
Adding additional string vs. look up table | Excel Worksheet Functions | |||
SUMPRODUCT - Additional criteria | Excel Worksheet Functions | |||
Additional Sumproduct Criterias | Excel Worksheet Functions |