How to Count IF something AND something in Excel

[HowHow]

Using Excel 2000, I need to count only if both of the criteria are met. Just for example, I have a list of fruits with price and dates beside it.

If I want to COUNT only when the price is more than $5 AND the date are blank.
[HTML]
Fruit Price Date
Apple $3 Jul-08
Kiwi $6 Jun-08
Orange $8
Apple $6 Jan-08
Kiwi $2 Jul-08
Apple $7
Kiwi $4

[/HTML] How to write the formula in excel so that I get "2" in the result column?

[RedSon]

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

[HowHow]

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

[Brosert]

You can construct the two conditions using AND

AND({condition1}, {condition2})
 

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

[HowHow]

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

=IF(C2="Jul-08",1,2)

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?

[Brosert]

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)

[HowHow]

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....

[Brosert]

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....

[Brosert]

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)

[HowHow]

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)

[HowHow]

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?

[Brosert]

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.....

 
A      B          C              D        E             F
1     Orange     Jul-08         $3       -7       =if(AND(C1=96930, E1<0),1,0)
 
 
=count(F:F)
 
 

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.

[HowHow]

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

[HTML]
Fruit Date Negative
Apple Jul-08
Kiwi Jul-08
Orange 1
Apple Jan-08
Kiwi Jul-08 1
Apple
Kiwi
Banana Aug-08 1
Grape Aug-08 1
[/HTML]

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.

[Brosert]

That is a bit more complex....


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

=sum(if(B1:b25=96930, if(C1:C25<>0,1,0),0)
 

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.....

[HowHow]

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

=IF(AND(C2=39630,D2=1),7,"") & IF(AND(C2=39661,D2=1),8,"") & IF(AND(C2=39692,D2=1),9,"")

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:

[HTML] Colume E Colume F
Jul-08 =countif(E2:E100,7)
Aug-08 =countif(E2:E100,8)
[/HTML]

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

[Brosert]

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

But great to hear it's working!!!

[RedSon]

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!