Please help

[_Landon]

Hello,

I’m having some trouble with a formula solution that I am stuck on.

I need to count a simple tally, if a row has been marked “MC” AND a value (number) has been entered in any combination to P,Q, or R then count as 1.

My formula is attached, and the issue is the value has to be numerically 0 for it to function. Currently it will count a value if “MC” is marked on the row and no numbers have been inputted. I don’t want zeros all over the sheet, I want the sheet to remain blank unless info has been added.

I would greatly appreciate any other ideas or ways to simply count.
Thank you!!

 

[Scott Huish]

Since column T is the sum of those columns, just check on that:

=COUNTIFS(U6:U15,"MC",T6:T15,">0")

 

[_Landon]

Column T is the sum of column N-R.

That won’t work for me because I am trying to extract data from P,Q,R only.

 

[Joe4]

How exactly are the cells in column P-R being populated?
It sounds to me like maybe they are not really blank.

Are there formulas in those cells?
Or is there are blank spaces?

 

[_Landon]

How exactly are the cells in column P-R being populated?
It sounds to me like maybe they are not really blank.

Are there formulas in those cells?
Or is there are blank spaces?

Those cells are blank, populated blank. The numbers entered are manually entered, there are no formulas in P-R.

My issue is my current formula is looking for those cells to have a numerical value of 0, which they do not have as blank cells.

 

[Joe4]

I am bit confused by your example and explanation, and am not exactly sure what you are trying to count, as there seems to be conflicting information.
What exactly are you trying to count, the non-blank values, the values greater than zero, or the values equal to zero?
I do not see any records in your example with zeroes in them.

 

[_Landon]

I am bit confused by your example and explanation, and am not exactly sure what you are trying to count, as there seems to be conflicting information.
What exactly are you trying to count, the non-blank values, the values greater than zero, or the values equal to zero?
I do not see any records in your example with zeroes in them.

Let me try to explain with some more context.

This is a flight tracker sheet. Each row is for a single flight and the numbers are for time flown in specific modes.

D - Day
H/w - Weather
N - Night Unaided
NS - Night System
NG - Night Goggles

Flight status in column U is a drop down list.

MC - Mission Complete
NC - Not complete
CX - Cancelled

I am trying to calculate the total number of flights that are either MC or NC by Day or Night.

A single flight row can have all modes or only certain modes flown.
Any mode of day (N,O) flown MC or NC (U) will need to calculated as 1
Any mode of night (P,Q,R) flown MC or NC (U) will need to be calculated as 1.

My current formulae is counting anything other than 0 as a factor. But it isn’t working for some reason. If I can figure out the formula for calculating the number of night flights I can redo the formula for day.

 

[Joe4]

I see the challenge. It can get a little tricky. You might need to use SUMPRODUCT instead of COUNTIFS.
However, if you add a temp column to sum columns P-R for that row, we can get it to work, like this:

So the formula for cell V6 is:

=SUM(P6:R6)

And the formula for cell U2 (the count you want is):

=COUNTIFS(U6:U15,"MC",V6:V15,">0")

Are you able to make use of a Temp column, like this? You can even hide the temp column, if you like (it does not need to be visible).

 

[_Landon]

I see the challenge. It can get a little tricky. You might need to use SUMPRODUCT instead of COUNTIFS.
However, if you add a temp column to sum columns P-R for that row, we can get it to work, like this:

View attachment 120279

So the formula for cell V6 is:

=SUM(P6:R6)

And the formula for cell U2 (the count you want is):

=COUNTIFS(U6:U15,"MC",V6:V15,">0")

Are you able to make use of a Temp column, like this? You can even hide the temp column, if you like (it does not need to be visible).

I did think of that but I was trying to avoid doing unnecessary work.

I would need to have a temp column for combined day flights =SUM(N#,O#)

As well as a temp column for combined night flights
=SUM(P#,Q#,R#)

But, let me try and work that real quick and see if I can do that.

 

[Joe4]

This may be helpful, as it shows you how you can do "OR" like conditions in COUNTIFS formulas.

Excel COUNTIF and COUNTIFS with OR logic

Learn how to use Excel COUNTIF and COUNTIFS functions to handle multiple conditions with OR logic.

www.ablebits.com