Formula Countif then subtract then add

Tabitha W.

I'm working on a sheet where I need to count if the PEP was received.

There are special circumstances where it may be sent back for corrections then resubmitted.

I want to count when we receive it but then subtract it back out if it is sent back but then add it if it is received back again. I've tried several COUNTIF functions where I then subtract and later add if applicable but I can't seem to get the numbers to work out. Here is what I tried that is not working:

=COUNTIF([PEP Not Received]:[PEP Not Received], >0,-[sent back]+[received back])

Kelly Moore

=COUNTIF([Received From AA]@row,ISDATE(@cell))+COUNTIF([Sent Back]@cell,ISDATE(@cell))-COUNTIF([Received Back]@cell, ISDATE(@cell))

If it's unparseable, double check my spelling of your column names - I think that is the issue with the one above. Using your request for the arithmetic, this will return a 1 if you have a date in the Received from AA column. It will yield a 2 if there is a date in the [Sent Back] column and [Received Back] is empty, and back to a 1 if all of the columns have a date.

Kelly Moore

Hi Tabitha,

In your formula, you didn't close off your first COUNTIF- and your column names aren't exact matches. If you're looking for a summary value, I believe you're looking for the count of each of those categories. If so, your formula would look something like this

=COUNTIF([PEP Not Received]:[PEP Not Received], >0) - COUNTIF([Sent Back]:[Sent Back], <>"") + COUNTIF([Received Back]:[Received Back], <>"")

If I misinterpreted what you're looking for, let me know and we'll try again.

Tabitha W.

Thank you KDM!

When I try to utilize the formula you suggested, it seems to calculate for the entire sheet. I only want it to do it for the row.

Kelly Moore

Hi Tabitha

I wondered if you were looking at the entire sheet or at the individual row.

Will this work for you?

=IF([PEP Not Received]@row > 0, [PEP Not Received]@row - [Sent Back]@row + [Received Back]@row)

Tabitha W.

Here is what is now populating with the above formula-

#Circular Reference

Kelly Moore

Sorry Tabitha

Are you trying to put this formula in the [PEP Not Received] column? That would give you a circular reference. Your screenshot shows '1' in the cells of [PEP Not Received]. How is that value obtained?

Tabitha W.

Yes, I was trying to replace the formula since it isn't doing what I needed it to do. I need it to report on what I originally asked above- I hope this makes sense.

Kelly Moore

Sorry, I'm sure you're frustrated, let's start over. I was confused by your original formula and the existing data in your screenshot. I need to understand the workflow a little bit.

If the [Date Received] column is blank, does that mean you haven't received the PEP?

If the [Date Received] is filled in, And [Sent Back] is filled in but [Received Back] is blank, does that mean you haven't received the PEP?

I tried a formula based on what I think is happening above.

=IF([Received From]@row = "", 1, IF(ISDATE([Sent Back]@row), IF(AND(ISDATE([Sent Back]@row), ISDATE([Received Back]@row)), 0, 1), 0))

My formula assumes if the [Received From] is blank, count this as a missing PEP. If [Sent Back] is filled in but [Received Back] is empty, count this as a missing PEP. I assumed you would not have a completed [Received Back] and still have a blank [Received From]

fingers crossed that my assumptions in your workflow were correct

Tabitha W.

I really appreciate you helping me work through it!

so I tried the formula you gave and it is returned as unparsable.

The workflow:

We receive the PEP we count it.

We receive the PEP it has an error, it is returned and we shouldn't count it

We receive the PEP it has an error, it is returned, it wasn't counted but now it is received back and is corrected, we count it.

The current formula in the PEP Not Received column does no take into account the last two scenarios and I need it to which is why I was trying to replace it with the new formula.

Current formula: =COUNTIF([Received From AA]@row:[Received From AA]@row, "<>")

I hope this makes sense and again, thank you for trying to help me work through it!

Kelly Moore

=COUNTIF([Received From AA]@row,ISDATE(@cell))+COUNTIF([Sent Back]@cell,ISDATE(@cell))-COUNTIF([Received Back]@cell, ISDATE(@cell))

If it's unparseable, double check my spelling of your column names - I think that is the issue with the one above. Using your request for the arithmetic, this will return a 1 if you have a date in the Received from AA column. It will yield a 2 if there is a date in the [Sent Back] column and [Received Back] is empty, and back to a 1 if all of the columns have a date.