Count with multi criteria

[ShannonFarris]

I am new to the site sorry if I post incorrectly

Here is my questions:
I wish to count the number of trees that flower.
This is my spreadsheet setup

------------- A------------- B-------------------C
1----------Type----------Flowers---------- Color
-------------------------- -y/n--------------------
2--------Flower------------Y----------------Pink
3--------Flower------------N---------------------
4 -------Tree---------------Y----------------Pink
5--------Tree---------------N
This my formulas that I have tried:
=SUMPRODUCT((A2:C5="tree")+(A2:C5="Y")) It gives the answer of 4
=SUMPRODUCT(--(A2:C5={"Tree","Y"})) This gives the answer #N/A

Please advise
What if I want tress that flower pink?

The next question I wish to count the times between 8:00 am to 8:59 am
This is the data:
8:10
8:27
9:10

The answer should be 2. How would I write this?

Thanks…to those who know the answer. I just know enough to know ther
is a way !!!

Shannon:confused

Attachment filename: trees.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=59419

 

[Domenic]

Hi,

Try,

=SUMPRODUCT((A2:A5="Tree")*(B2:B5="Y"))

and

=SUMPRODUCT((A1:A3>=C1)*(A1:A3<=D1))

where C1 contains your start time and D1 your end time.

Hope this helps!

 

[Biff]

Hi Shannon!

Just some small tweaks:

=SUMPRODUCT((A2:A5="tree")*(B2:B5="Y"))

Or, you could write that formula like this:

=SUMPRODUCT(--(A2:A5="tree"),--(B2:B5="Y"))

To add the pink condition:

=SUMPRODUCT(--(A2:A5="tree"),--(B2:B5="Y"),--
(C2:C5="pink"))


To count number of entries between two times:

=SUMPRODUCT(--(A1:A3>=TIME(8,0,0)),--(A1:A3<=TIME(8,59,0)))

This includes times = 8:00am and 8:59am. Just change the