Home |
Search |
Today's Posts |
#8
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi Tetthless mama
Working like a charm....... Many thx Duke, Mike, Arvi - Thank you for responding. Regards EricB "Teethless mama" wrote: =SUMPRODUCT(--(sscrq!C2:C60000="Debit Order"),--(ISNUMBER(SEARCH("Not Affordable",sscrq!AF2:AF60000)))) "EricB" wrote: Hi Duke & Mike I possibly gave too little detail of the extent of the sheet, my columns range to AF (Not A & B as stipulated below): Here is the 'modified formula', =SUMPRODUCT(--(sscrq!C2:C60000=€¯Debit Order€¯),--(left(sschq!AF2:AF60000,18)=€¯Not Affordable due to")) EXCEL is spitting errors at me. What does the phrase "LEFT" and number "18" or "26" refer to in your formula Regards EricB "Duke Carey" wrote: Well, in addition to some missing closing quotes, the logic in the second formula will ALWAYS drive you to a zero result, since you're looking at mutually exclusive alternatives in the the same column (B2:B60000=€¯Not Affordable due to nett)*(B2:B60000=€¯Not Affordable due to gross)) For the first one, try =SUMPRODUCT(--(A2:A60000=€¯Debit Order€¯),--(left(B2:B60000,18)=€¯Not Affordable due to")) "EricB" wrote: Hi Im trying to count various items in a column, but not all. I can either do this by stipulating a range of items or using a wildcard. However, Sumproduct is not returning any results when I use more than one item/criteria for a specified column. Example: =SUMPRODUCT((A2:A60000=€¯Debit Order€¯)*(B2:B60000=€¯Not Affordable due to*)) Or =SUMPRODUCT((A2:A60000=€¯Debit Order€¯)*(B2:B60000=€¯Not Affordable due to nett)*(B2:B60000=€¯Not Affordable due to gross)) Both results gives me a 0 result. I tried replacing the * with ,-- and , and + Any advice? Regards EricB |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
sumproduct exclude count | Excel Worksheet Functions | |||
sumproduct exclude count | Excel Worksheet Functions | |||
sumproduct exclude count | Excel Worksheet Functions | |||
SUMPRODUCT - (amended) Exclude LAST Row of Matched Criteria (Month & Year) | Excel Worksheet Functions | |||
SUMPRODUCT - Exclude LAST Row of Matched Criteria (Month & Year) | Excel Worksheet Functions |