Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct for multiple criteria in different columns
I have been trying to use this site to run a sumproduct formula for the below:
A B C D E F G projid status targetedstartdate targetedcompldate amountbilled Onsched. I want to count how many times the instances of a number with the prefix B00000 or BPR000000 occur in column A (called projid) and when "Late" occurs in column G (called Onsched.) There are 4300 rows of data. my formula is: =SUMPRODUCT(A2:A4300="B*")*(G2:G4300="Late") I don't get a sum of the values. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct for multiple criteria in different columns
*Maybe* this:
=SUMPRODUCT(--(LEFT(A2:A4300)="B"),--(G2:G4300="Late")) If there are entries other than B00000 or BPR000000 that start with "B" they may be calculated. In that case we'll have to refine the condition. -- Biff Microsoft Excel MVP "gbpg" wrote in message ... I have been trying to use this site to run a sumproduct formula for the below: A B C D E F G projid status targetedstartdate targetedcompldate amountbilled Onsched. I want to count how many times the instances of a number with the prefix B00000 or BPR000000 occur in column A (called projid) and when "Late" occurs in column G (called Onsched.) There are 4300 rows of data. my formula is: =SUMPRODUCT(A2:A4300="B*")*(G2:G4300="Late") I don't get a sum of the values. Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct for multiple criteria in different columns
Try it this way...
=SUMPRODUCT((LEFT(A2:A43)="B")*(G2:G43="Late")) Although it wouldn't make a difference to your result, you were missing a set of parentheses around the multiplication of the two logical expressions, so SUMPRODUCT took the first logical expression as its argument and multiplied its result times the second logical expression (which, being outside the SUMPRODUCT function, wouldn't be an array, I guess). Rick "gbpg" wrote in message ... I have been trying to use this site to run a sumproduct formula for the below: A B C D E F G projid status targetedstartdate targetedcompldate amountbilled Onsched. I want to count how many times the instances of a number with the prefix B00000 or BPR000000 occur in column A (called projid) and when "Late" occurs in column G (called Onsched.) There are 4300 rows of data. my formula is: =SUMPRODUCT(A2:A4300="B*")*(G2:G4300="Late") I don't get a sum of the values. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct for multiple criteria in different columns
they both returned the same value of 214 (after I changed Ricks 43 to 4309).
Thanks guys, I am still not sure why mine did not work. If I need to substitute the B* for say BPR* would this still work? "Rick Rothstein (MVP - VB)" wrote: Try it this way... =SUMPRODUCT((LEFT(A2:A43)="B")*(G2:G43="Late")) Although it wouldn't make a difference to your result, you were missing a set of parentheses around the multiplication of the two logical expressions, so SUMPRODUCT took the first logical expression as its argument and multiplied its result times the second logical expression (which, being outside the SUMPRODUCT function, wouldn't be an array, I guess). Rick "gbpg" wrote in message ... I have been trying to use this site to run a sumproduct formula for the below: A B C D E F G projid status targetedstartdate targetedcompldate amountbilled Onsched. I want to count how many times the instances of a number with the prefix B00000 or BPR000000 occur in column A (called projid) and when "Late" occurs in column G (called Onsched.) There are 4300 rows of data. my formula is: =SUMPRODUCT(A2:A4300="B*")*(G2:G4300="Late") I don't get a sum of the values. Any ideas? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct for multiple criteria in different columns
In that case use:
=SUMPRODUCT(--(LEFT(A2:A4300,3)="BPR"),--(G2:G4300="Late")) Or, use cells to hold the criteria: J1 = B or BPR K1 = Late =SUMPRODUCT(--(LEFT(A2:A4300,LEN(J1))=J1),--(G2:G4300=K1)) -- Biff Microsoft Excel MVP "gbpg" wrote in message ... they both returned the same value of 214 (after I changed Ricks 43 to 4309). Thanks guys, I am still not sure why mine did not work. If I need to substitute the B* for say BPR* would this still work? "Rick Rothstein (MVP - VB)" wrote: Try it this way... =SUMPRODUCT((LEFT(A2:A43)="B")*(G2:G43="Late")) Although it wouldn't make a difference to your result, you were missing a set of parentheses around the multiplication of the two logical expressions, so SUMPRODUCT took the first logical expression as its argument and multiplied its result times the second logical expression (which, being outside the SUMPRODUCT function, wouldn't be an array, I guess). Rick "gbpg" wrote in message ... I have been trying to use this site to run a sumproduct formula for the below: A B C D E F G projid status targetedstartdate targetedcompldate amountbilled Onsched. I want to count how many times the instances of a number with the prefix B00000 or BPR000000 occur in column A (called projid) and when "Late" occurs in column G (called Onsched.) There are 4300 rows of data. my formula is: =SUMPRODUCT(A2:A4300="B*")*(G2:G4300="Late") I don't get a sum of the values. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum with multiple criteria using SUMPRODUCT | Excel Worksheet Functions | |||
SumProduct With Multiple criteria | Excel Worksheet Functions | |||
sumproduct 2 columns based on criteria in 3rd column | Excel Discussion (Misc queries) | |||
multiple criteria in if or sumproduct | Excel Worksheet Functions | |||
sumproduct using multiple criteria | Excel Worksheet Functions |