| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF
Need a COUNT of how many loans were either Booked OR Cancelled within the
Central Division. Alternately - need a COUNT of how many loans were Declined in the Central Division. I have tried this, but now out of options - Help! =COUNTIF(AND($F$F1500, "Booked"OR"Cancelled", $B$2:$B$1500, "Central") -- Pappas |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
Hi Pappas,
Try something like ... =COUNTIF(range_central_division,"Booked")+COUNTIF( range_central_division,"Cancelled") =COUNTIF(range_central_division,"Declined") Wkr, JP "Pappas" . wrote in message ... Need a COUNT of how many loans were either Booked OR Cancelled within the Central Division. Alternately - need a COUNT of how many loans were Declined in the Central Division. I have tried this, but now out of options - Help! =COUNTIF(AND($F$F1500, "Booked"OR"Cancelled", $B$2:$B$1500, "Central") -- Pappas |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
try
=sumproduct((F$F1500={"Booked","Cancelled"})*($B$2 :$B$1500="Central")) -- Don Guillett Microsoft MVP Excel SalesAid Software "Pappas" . wrote in message ... Need a COUNT of how many loans were either Booked OR Cancelled within the Central Division. Alternately - need a COUNT of how many loans were Declined in the Central Division. I have tried this, but now out of options - Help! =COUNTIF(AND($F$F1500, "Booked"OR"Cancelled", $B$2:$B$1500, "Central") -- Pappas |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
Hi
Try =SUMPRODUCT(((F1:F1500="Booked")+(F1:F1500="Cancel led"))*(B1:B1500="central")) Add absolute references as needed, note your sample formula is missing the row for the first F and the two ranges are not equal - the second starts on row 2 - they need to be the same length. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Pappas" wrote: Need a COUNT of how many loans were either Booked OR Cancelled within the Central Division. Alternately - need a COUNT of how many loans were Declined in the Central Division. I have tried this, but now out of options - Help! =COUNTIF(AND($F$F1500, "Booked"OR"Cancelled", $B$2:$B$1500, "Central") -- Pappas |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
You can use Don's version but you must make the changes I mentioned:
=SUMPRODUCT((B1:B13="central")*(F1:F13={"Cancelled ","Booked"})) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Shane Devenshire" wrote: Hi Try =SUMPRODUCT(((F1:F1500="Booked")+(F1:F1500="Cancel led"))*(B1:B1500="central")) Add absolute references as needed, note your sample formula is missing the row for the first F and the two ranges are not equal - the second starts on row 2 - they need to be the same length. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Pappas" wrote: Need a COUNT of how many loans were either Booked OR Cancelled within the Central Division. Alternately - need a COUNT of how many loans were Declined in the Central Division. I have tried this, but now out of options - Help! =COUNTIF(AND($F$F1500, "Booked"OR"Cancelled", $B$2:$B$1500, "Central") -- Pappas |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
All suggestions worked beautifully; however, it was only part of my formula .
.. . . this is what I have come up with so far . . . . now I need help with just the last part underlined. =SUMPRODUCT((($F$2:$F$1500={" Booked"," Cancelled"})*($B$2:$B$1500=" North Mississippi")*(($E$2:$E$150033%)*(F21500<=35.99%) ))) ______________________________________ I need to add one additional . . . if between 33% and 35.99% . . . . but the result is wrong - so I am know I am not doing something right. Thanks!!! Pappas -- Pappas "Shane Devenshire" wrote: You can use Don's version but you must make the changes I mentioned: =SUMPRODUCT((B1:B13="central")*(F1:F13={"Cancelled ","Booked"})) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Shane Devenshire" wrote: Hi Try =SUMPRODUCT(((F1:F1500="Booked")+(F1:F1500="Cancel led"))*(B1:B1500="central")) Add absolute references as needed, note your sample formula is missing the row for the first F and the two ranges are not equal - the second starts on row 2 - they need to be the same length. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Pappas" wrote: Need a COUNT of how many loans were either Booked OR Cancelled within the Central Division. Alternately - need a COUNT of how many loans were Declined in the Central Division. I have tried this, but now out of options - Help! =COUNTIF(AND($F$F1500, "Booked"OR"Cancelled", $B$2:$B$1500, "Central") -- Pappas |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
Jp Ronse, Don Guillett, & Shane . . . . Thanks for the initial HELP - It
worked Beautifully!!!!! However on my reply - Nevermind - I figured it out - Thanks again!!!! This is what I came up with and it worked great!!!! Thank you!!! =SUMPRODUCT((($F$2:$F$1500={" Booked"," Cancelled"})*($B$2:$B$1500=" North Mississippi")*(($E$2:$E$150030%)*($E$2:$E$1500<=3 2.99%)))) -- Pappas "Pappas" wrote: All suggestions worked beautifully; however, it was only part of my formula . . . . this is what I have come up with so far . . . . now I need help with just the last part underlined. =SUMPRODUCT((($F$2:$F$1500={" Booked"," Cancelled"})*($B$2:$B$1500=" North Mississippi")*(($E$2:$E$150033%)*(F21500<=35.99%) ))) ______________________________________ I need to add one additional . . . if between 33% and 35.99% . . . . but the result is wrong - so I am know I am not doing something right. Thanks!!! Pappas -- Pappas "Shane Devenshire" wrote: You can use Don's version but you must make the changes I mentioned: =SUMPRODUCT((B1:B13="central")*(F1:F13={"Cancelled ","Booked"})) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Shane Devenshire" wrote: Hi Try =SUMPRODUCT(((F1:F1500="Booked")+(F1:F1500="Cancel led"))*(B1:B1500="central")) Add absolute references as needed, note your sample formula is missing the row for the first F and the two ranges are not equal - the second starts on row 2 - they need to be the same length. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Pappas" wrote: Need a COUNT of how many loans were either Booked OR Cancelled within the Central Division. Alternately - need a COUNT of how many loans were Declined in the Central Division. I have tried this, but now out of options - Help! =COUNTIF(AND($F$F1500, "Booked"OR"Cancelled", $B$2:$B$1500, "Central") -- Pappas |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
| edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
| COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
| Need a little help w/ COUNTIF | Excel Worksheet Functions | |||
| COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Linear Mode
