Countif with AND formula

Hi All, I have the following simple formula in a cell on another tab of a worksheet. Unfortunately it is returning a #VALUE! error.
=COUNTIF(Engineering!G:G,"ecn")*AND(Engineering!I:I,"yes")
The part prior to *AND works fine on its own.

The aim is to have the countif part only count if there is a cell in the same row in the I column with yes in it.

Can anyone advise where I am going wrong?

I will also be adding a third part which check another cell in the same row has a date matching a date in cell a1.

thanks

Try the countifS function

Many thanks guys, The one from Alkey worked a treat. Now i will attempt to add a date reference also.
thanks again.

struggling a little with the date part.

I am looking to have another cell in column H checked to make sure the date falls in the range i require.
So in cell A1 i will enter 04/2014 which will format to Apr-14 I then need to add to the above formula to check that the date cell in the same row has a date that falls in April 2014. The format of the date is currently dd/mm/yyyy.

Again any help is really appreciated.
thanks

Hi Yes, trying to include it in same formula. So now I have the following =COUNTIFS(Engineering!G:G,"ecn",Engineering!I:I,"yes",Engineering!H:H,">="&B1).
Hopefully this will only count if the cells are equal to ECN, Yes and fall in the month of the date I enter into B1.
Yes its working as I hoped.

Many thanks Alkey, I would truly buy you a pint if I could.

Ahhh, just spotted another problem. The date is choosing equal to or bigger than the date I enter. If I try and recall the data for say January this year then it counts from January onwards.
I removed the > and thought this to be working for a while but now know it is because I was using test data with a date of 01/01/2014 or 01/02/2014 Etc. When I add the date to the date search cell as 01/2014 for January 2014 it actually records a date of 01/01/2014 instead of simply 01/2014 as in mm/yyyy. I formatted the cell as mm/yyyy thinking this would work but alas not. I will tinker with the date formats and try to have the data store as 01/2014 for month/year. But then unsure if the count will work as I need the date to be based on any day of the given month and year.

Any thoughts on this one?

Even if you format the date as mm/yyyy it will still be stored as the exact day it was entered. You'll need to make another column to get the month. Use the following formula:

Formula:

Please Login or Register  to view this content.

A1 refers to the cell that has your date in it. Your SUMIFS should refer to the cell with the formula in it. Let me know if it works.

Hi, I still want the date in my original cell to be an exact date. So I could add another column next to the date column in my original sheet and add your suggested formula. This would convert all the dates to the first of the same month, then i could use my previous formula the way it was as this is already using the same date format?
I will try this but would rather not add in an extra column, I would rather have the formula altered to pull this data out as required.

many thanks

Could you not hide the column that you create? You can work the formula I gave you into the countif but it'd probably have to become an array as it has to operate on each item in the column you've specified and I'm not that good!

Thanks