Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula help
These are the two formulas I'm using. Do you see any
errors that pop out at you? If not, can you help me figure out a way to make this work? Column F is gender so the only options are M or F. Column G is ethnicity so the only options are A,B,C,H,N,M,and O. Column T is the level of placement, so the options are 1,2,3,4, or 5. The formulas are intended to exclude data without these criteria in a total count. =SUMPRODUCT(--(F6:F232="F"),--(G6:G232="C"),-- (T6:T232="1")) =SUMPRODUCT(--(G6:G232="C"),--(T6:T232="1")) Column G is Ethnicity where the only options are A,B,C,H,N,M, and O. Column A is the date of the referral. This particular problem is that I need it to calculate how many clients met these criteria in a specific month. =SUMPRODUCT(--(G6:G464="C"),--(TEXT(A6:A464,"mmm")="Oct") *1) =SUMPRODUCT(--(F6:F464="F"),--(G6:G464="C"),--(TEXT (A6:A464,"mmm")="Oct")*1) |
#2
|
|||
|
|||
Brandy wrote:
These are the two formulas I'm using. Do you see any errors that pop out at you? If not, can you help me figure out a way to make this work? Column F is gender so the only options are M or F. Column G is ethnicity so the only options are A,B,C,H,N,M,and O. Column T is the level of placement, so the options are 1,2,3,4, or 5. The formulas are intended to exclude data without these criteria in a total count. =SUMPRODUCT(--(F6:F232="F"),--(G6:G232="C"),-- (T6:T232="1")) =SUMPRODUCT(--(G6:G232="C"),--(T6:T232="1")) Remove double quotes around 1. Column G is Ethnicity where the only options are A,B,C,H,N,M, and O. Column A is the date of the referral. This particular problem is that I need it to calculate how many clients met these criteria in a specific month. =SUMPRODUCT(--(G6:G464="C"),--(TEXT(A6:A464,"mmm")="Oct") *1) =SUMPRODUCT(--(F6:F464="F"),--(G6:G464="C"),--(TEXT (A6:A464,"mmm")="Oct")*1) There is no need for the *1 bit in the foregoing formulas. You didn't specify any problem that you have regarding the latter two. |
#3
|
|||
|
|||
Thanks very much! The latter two were working, I just
entered the wrong data. The first two are not working at all - and the removal of the "" around the 1 did not change anything. Any other ideas? -----Original Message----- Brandy wrote: These are the two formulas I'm using. Do you see any errors that pop out at you? If not, can you help me figure out a way to make this work? Column F is gender so the only options are M or F. Column G is ethnicity so the only options are A,B,C,H,N,M,and O. Column T is the level of placement, so the options are 1,2,3,4, or 5. The formulas are intended to exclude data without these criteria in a total count. =SUMPRODUCT(--(F6:F232="F"),--(G6:G232="C"),-- (T6:T232="1")) =SUMPRODUCT(--(G6:G232="C"),--(T6:T232="1")) Remove double quotes around 1. Column G is Ethnicity where the only options are A,B,C,H,N,M, and O. Column A is the date of the referral. This particular problem is that I need it to calculate how many clients met these criteria in a specific month. =SUMPRODUCT(--(G6:G464="C"),--(TEXT(A6:A464,"mmm") ="Oct") *1) =SUMPRODUCT(--(F6:F464="F"),--(G6:G464="C"),--(TEXT (A6:A464,"mmm")="Oct")*1) There is no need for the *1 bit in the foregoing formulas. You didn't specify any problem that you have regarding the latter two. . |
#4
|
|||
|
|||
Duh!! Nevermind, wrong data. Everything works. Thanks
for your help! -----Original Message----- Brandy wrote: These are the two formulas I'm using. Do you see any errors that pop out at you? If not, can you help me figure out a way to make this work? Column F is gender so the only options are M or F. Column G is ethnicity so the only options are A,B,C,H,N,M,and O. Column T is the level of placement, so the options are 1,2,3,4, or 5. The formulas are intended to exclude data without these criteria in a total count. =SUMPRODUCT(--(F6:F232="F"),--(G6:G232="C"),-- (T6:T232="1")) =SUMPRODUCT(--(G6:G232="C"),--(T6:T232="1")) Remove double quotes around 1. Column G is Ethnicity where the only options are A,B,C,H,N,M, and O. Column A is the date of the referral. This particular problem is that I need it to calculate how many clients met these criteria in a specific month. =SUMPRODUCT(--(G6:G464="C"),--(TEXT(A6:A464,"mmm") ="Oct") *1) =SUMPRODUCT(--(F6:F464="F"),--(G6:G464="C"),--(TEXT (A6:A464,"mmm")="Oct")*1) There is no need for the *1 bit in the foregoing formulas. You didn't specify any problem that you have regarding the latter two. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Formula using check boxes | Excel Discussion (Misc queries) | |||
Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) | |||
I want the results of a formula to show in cell, NOT THE FORMULA! | Excel Discussion (Misc queries) | |||
Paste is is copying in formula, but display is wrong. | Excel Discussion (Misc queries) | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) |