How to Count IF something AND something in Excel

Did you read the help files for Excel on how to use the IF and AND in your formulas?

I tried but I still don't get it. Can you please suggest formular to me please?

You can construct the two conditions using AND

Then you can use them as the condition in your COUNTIF statement....

It still didn't work. Actually I try to test on IF function again and found even the single formular didn't work



Some of the dates in colume C are Jul-08, but the return values are all 2. Is that mean IF function cannot recognise date?

Cells are specially formatted to show the date.
This means that while the cell displays Jul-08, it doesn't have that value (that is the display is an interpretation of the cells value, rather than the actual value).
Modify the Cell (using the Format->Cells) to be of type 'General' and see what value comes up (should be an largish integer).
You can compare with this integer value....
I thought you were looking for empty cells, though, so you could use

=IF(A1="",1,2)

I changed the cell to general and it shows me 39630 instead of Jul-08, so I tested on it with the formula, =if(C2="39630",1,2) but it still gives me 2 instead of 1.

Actually I have another colume named "Negative" and some with number "1" in it. I need to calculate if the "Negative" colume has 1 and if it shows specific date.

I would imagine it is like =countif (D2:D200 = "1") AND (C2:C200 ="Jul-08). But now even test on the date it is not correct....

Thast's the other trick I forgot to mention
39630 != "39630"
Excel wants a number not a string (in this instance).
Tri:
=if(c2=39630,1,2)
(With not Quotes)

The countif statement probably gives you an error.
You need the following format:
=Countif(Range, And({Cond1},{Cond2}))

it is also a little more complicated, because your criteria is outside the range you are counting...I'll ahve a think on that one....

If you can afford another column, do the following:

in the toprow (I assuyme you have titles so I'll use 2)

=if(And(C2=69630, D2=1), 1, 0)

Fill down the length of that row.
Where you want the total do either
=Sum(E:E)
(Which you can do because we used 1)
OR
=COUNTIF(E:E,1)

Now it drives me crazy, I kept trying and it keeps giving error message.
I think I am confused with the cond1 and cond2, whether I can put a range in it as well...etc etc...

By using the example (table above), Orange has 1 in Negative colume and Kiwi, date Jul-08 has 1 in negative colume too. if I need to count how many of them are negative in Jul-08, what formula you would suggest to be? (sorry, I try to copy and paste the table from Excel to here but it didn't work)

I need to count the negative one for each month (of course because some of the negatives don't have a month), so if I add colume, it would mean adding 12 columes...is there a better way?

Not sure I understand...
If you only have 1 -ve column, you only need 1 count column (ie E shows 1 if D is -ve AND the date is what you want.....

If you have multiple -ve columns already (one for each month), you can extract the Month from the Date and use that to create a column reference to the cell you want to access - which is more complicated but not impossible.

I will just try to enter the data here so that you have a clearer idea of what I need:



Using these info, I know that in Jul, I have 1 negative, in Aug I have 2 negative...at the end, I can provide a record of Jan-08, no negative, feb-08, no negative....Jul-08, 1 negative, Aug-08,2 negative...etc..something like that.

That is a bit more complex....


I think (but haven't checked) You could use a nested if statement along the lines of



This works as follows:
count the return of "for each right date, for each negative return 1"....

of course, this will ignore whether your negative is 1,2,3,4....just whether it is there or not.....

Thank You!!! yes, I can use nest, got it.
First I only add one colume with the formula:



and so on, where 7 means July to me, 8 means Aug to me, 9 means September to me....etc for a financial year.

Then I have it in another colume with the month from Jul-08 to Jun-09, in colume next to it, I have this formula:



And now I got the result that I needed! Thanks a lot!

Truth be known, that wasn't quite what I meant....

But great to hear it's working!!!

Quote: Truth be known, that wasn't quite what I meant.... But great to hear it's working!!!

There is more then one way to skin a cat!