"Countif" with a Condition Question 1

[zidol]

In Column F, I am getting a total of "Refusals" based on the following formula:

Column F – Number of Refusals
=COUNTIF('Pivot Table'!F:F,"Treatment, No Transport")+COUNTIF('Pivot Table'!F:F,"No Treatment, No Transport")

Column I - List different types of Review Status, such as "Complete", "In Progress" and "Not Reviewed". I am trying to get the total number of "Refusal" that have been "Reviewed" by using the following formula:

=COUNTIF('Pivot Table'!F:F,"Treatment, No Transport")+COUNTIF('Pivot Table'!F:F,"No Treatment, No Transport")*('Pivot Table'!I:I<>"Not Reviewed")

Results are:
Refusal - 48
Reviewed - 48

The number of “Refusals” are correct , but the number of “Reviewed” should be 45.

What am I doing wrong?

 

[SkipVought]

HI,

Kind of difficult to help with this kind of problem without a data example.

But this looks suspect to me...

('Pivot Table'!I:I<>"Not Reviewed")

Your COUNTIF returns a single value. This expression is a ARRAY expression.

I'd opt for the SUMPRODUCT...

=SUMPRODUCT((category1="No treatment, No transport")*(category4<>"Not Reviewed"))

BTW, I use Named Ranges or Structured Tables exclusively rather than A1 notation in table analysis.

And you could have also done this with COUNTIFS()

COUNTIFS('Pivot Table'!F:F,"No Treatment, No Transport",'Pivot Table'!I:I<>"Not Reviewed")
Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[zidol]

Based on the sample database below using the formula:

=COUNTIF('Pivot Table'!F:F,"Treatment, No Transport")+COUNTIF('Pivot Table'!F:F,"No Treatment, No Transport")
I get 6 "Refusals" returned, which is correct.

I tried using the following formula to get a count of "Refusals" that have been "Reviewed" by using the above formula AND adding Column I does not equal "Not Reviewed".

=COUNTIF('Pivot Table'!F:F,"Treatment, No Transport")+COUNTIF('Pivot Table'!F:F,"No Treatment, No Transport")*('Pivot Table'!I:I<>"Not Reviewed")
I should get back 5 "Reviewed" but my formula is returning 6 "Reviewed", the same total as number of "Refusals"

Sample of Database:
Column F ..................................................................Column I
Patient Care Transferred ………………………………………….. Not Reviewed
Treatment, No Transport ………………………………………….. Not Reviewed
Patient Care Transferred ………………………………………….. Not Reviewed
Patient Care Transferred ………………………………………….. Closed
Treatment, No Transport ………………………………………….. In Progress
Patient Care Transferred ………………………………………….. Review Complete
Treatment, No Transport ………………………………………….. Review Complete
No treatment, No Transport ………………………………………….. Review Complete
Treatment, No Transport ………………………………………….. Review Complete
Treatment, No Transport ………………………………………….. Review Complete
Patient Care Transferred ………………………………………….. Closed
Patient Care Transferred ………………………………………….. Closed
Patient Care Transferred ………………………………………….. In Progress

I hope this makes it clearer.

Thanks!

 

[SkipVought]

Do you not have column headings on F & I?

Proper table structure requires unique column headings.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[zidol]

Yes they do have headings.

I seem to run into trouble when I have multiple criteria, e.g. "No Treatment,No Transport" , "Treatment,No Transport", as in the one I am trying to fix. The formula you sent me works but there is not multiple criteria in it.

I do have this formula for another column which works fine as well.

=COUNTIFS('Pivot Table'!D,"Chest Pain / Discomfort",'Pivot Table'!I:I,"<>Not Reviewed")

How to I have the other criteria included to return the correct results?

 

[Andrzejek]

Just a hint, zidol

You can show your data this way if you use TGML tag [ignore][pre] ... [/pre][/ignore]

[pre]
Column F Column I
Patient Care Transferred Not Reviewed
Treatment, No Transport Not Reviewed
Patient Care Transferred Not Reviewed
Patient Care Transferred Closed
Treatment, No Transport In Progress
Patient Care Transferred Review Complete
Treatment, No Transport Review Complete
No treatment, No Transport Review Complete
Treatment, No Transport Review Complete
Treatment, No Transport Review Complete
Patient Care Transferred Closed
Patient Care Transferred Closed
Patient Care Transferred In Progress[/pre]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[zidol]

Wow - that is great. Thanks!!

 

[SkipVought]

please use the PRE TGML tag to post examples...

[pre]
Column F  Column I
Patient Care Transferred Not Reviewed
Treatment, No Transport Not Reviewed
Patient Care Transferred Not Reviewed
Patient Care Transferred Closed
Treatment, No Transport In Progress
Patient Care Transferred Review Complete
Treatment, No Transport Review Complete
No treatment, No Transport Review Complete
Treatment, No Transport Review Complete
Treatment, No Transport Review Complete
Patient Care Transferred Closed
Patient Care Transferred Closed
Patient Care Transferred In Progress

[/pre]

This formula returns 1 for your example...

=SUMPRODUCT((Column_F="No treatment, No transport")*(Column_I<>"Not Reviewed"))

...where Column F & Column I are actual column headings and Column_F & Column_I are the Named Ranges for the data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Oh YES. Your formula returns 6 and it should!

=COUNTIF(Column_F,"Treatment, No Transport")+COUNTIFS(Column_F,"No Treatment, No Transport",Column_I,"<>Not Reviewed")

5 for the first COUNTIF and 1 for the COUNTIFS (No treatment, No Transport...Review Complete)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[zidol]

It Works!

Thanks for all your help!!