ExcelBanter - COUNTIF

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - COUNTIF (https://www.excelbanter.com/excel-worksheet-functions/241613-countif.html)

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

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

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

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

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

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