Formula to use to count the number of cells in a column which meet three sets of criteria

Hi,

I'm looking for a formula which will count the number of times a value of less than 0 appears in column AT, but will only count this if the value 1 does not appear in the corresponding row in column E and column F. e.g.

E F AT

7 1 -4
8 0
9 -3
10 1 -2
11 -1

In the above example, the calculation would exclude rows 7 and 10 as there is a "1" in column E or F. I would therefore count rows 9 & 11 with a value below 0 and the answer would be 2.

Thanks

Hi Benishiryo,

I tried the formula, but unfortunately it didn't work. How do I attach an excel file to this thread (sorry to ask what is probably obvious!)

Many thanks,

Hi Tony,

Unfortunately this doesn't seem to work either. I only want it to count the negative values in column AT if there isn't a 1 in column E or in column F. Was wondering if I need an "AND" or "OR" somewhere in the countif formula.

I'll try! I have three columns of data. I want to count the number of times that a negative figure occurs in the third column (i.e countif(AT7:AT141<0), but I want the count to exclude any of the negative values if the number 1 appears in either of the first 2 columns.

Therefore, if cell A1 was empty, Cell B1 was empty but Cell C1 had a negative value, the answer would be 1.
If Cell A1 contained a 1, Cell B1 was empty but Cell C1 had a negative value, the answer would be 0 (as cell C1 would be ignored due to the 1 in Cell A1)
If Cell A1 was empty, Cell B1 contained a 1 but Cell C1 had a negative value, the answer would be 0 (as cell C1 would be ignored due to the 1 in Cell A1)

Many thanks

Hi Tony,

I realised when I went back to the sheet this morning that I was forgetting to reference the formula to the correct worksheet (as the summary was on a different sheet!). Felt such a fool and your formula now works perfectly thanks, so many many thanks for your help. I have a fairly similar query on the same piece of work and wondered if you may be able to help with that too. I'm counting the number of times particular text appears in a column, but as with the previous query, I want the count to ignore any rows where a 1 appears in column E or F of the sheet. I found a formula on line which counts the text successfully, but I'm not sure how to expand this so that it omits any rows with a 1 in column E or F. The formula I'm using is as follows: -

=SUMPRODUCT((LEN(Employed!AU$7:AU$141)-LEN(SUBSTITUTE(Employed!AU$7:AU$141,"Sick","")))/LEN("Sick"))

Many thanks,

It works! You're a genius. With my humble knowledge of excel I don't even know what the formula means, but it works and I can now complete the piece of work I'm doing successfully.

Thank you so much.