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

1. ## 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  Register To Reply

2. ## Re: Formula to use to count the number of cells in a column which meet three sets of crite

hi Dhabitude. try to upload a sample excel file next time as there the data is skewed when done here. maybe something like:
=COUNTIFS(AT:AT,"<0",F:F,"<>1")  Register To Reply

3. ## Re: Formula to use to count the number of cells in a column which meet three sets of crite

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,  Register To Reply

4. ## Re: Formula to use to count the number of cells in a column which meet three sets of crite

Maybe this...

=COUNTIFS(AT7:AT11,"<0",E7:E11,"<>1",F7:F11,"<>1")  Register To Reply

5. ## Re: Formula to use to count the number of cells in a column which meet three sets of crite

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.  Register To Reply

6. ## Re: Formula to use to count the number of cells in a column which meet three sets of crite

The first part of my forum name is quite interested in this statement:

"if there isn't a 1 in column E or in column F"

Can you explain in other words what you mean by this logical construction?

Regards  Register To Reply

7. ## Re: Formula to use to count the number of cells in a column which meet three sets of crite

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  Register To Reply

8. ## Re: Formula to use to count the number of cells in a column which meet three sets of crite

That's what I thought. But that's logically identical to saying:

"Count the number of times that a negative figure occurs in the third column AND neither the first nor the second column contains a 1"

i.e. Tony Valko's solution.

Regards  Register To Reply

9. ## Re: Formula to use to count the number of cells in a column which meet three sets of crite

Can you post a SMALL sample file with about 20 rows worth of data and tell us what result you expect?  Register To Reply

10. ## Re: Formula to use to count the number of cells in a column which meet three sets of crite

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,  Register To Reply

11. ## Re: Formula to use to count the number of cells in a column which meet three sets of crite

Try this array formula**:

=SUM(IF(Employed!E\$7:E\$141<>1,IF(Employed!F\$7:F\$141<>1,(LEN(Employed!AU\$7:AU\$141)-LEN(SUBSTITUTE(Employed!AU\$7:AU\$141,"Sick","")))/LEN("Sick"))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.  Register To Reply

12. ## Re: Formula to use to count the number of cells in a column which meet three sets of crite

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.  Register To Reply

13. ## Re: Formula to use to count the number of cells in a column which meet three sets of crite

You're welcome. Thanks for the feedback!   Register To Reply