please help with dcounta

D

doyree

i have following condition
please help!!!

Apple DEAD
Apple LIVE
Apple DEAD
Orange DEAD
Orange LIVE
Peach DEAD
Peach DEAD
Peach LIVE

how can i get a value for
# of dead apples, orange, and peach
# of live apples, orange and peach
not total but each individual value

thank you very much!!!!
 
K

Kamil

Hi,
1. You need headers in database. For Example Fruits and Condition. So the
table should now looks like this:

A B
Fruit State
Apple DEAD
Apple LIVE
Apple DEAD
Orange DEAD
Orange LIVE
Peach DEAD
Peach DEAD
Peach LIVE

2. Then You should repeat the headers name for the fruits and list the
condition bellow them. For example like this:

A B C D E
Fruit State Fruit State
Apple DEAD Apple DEAD
Apple LIVE
Apple DEAD
Orange DEAD
Orange LIVE
Peach DEAD
Peach DEAD
Peach LIVE

3. In no mather which cell insert formula:

=DCOUNTA(A1:B9;B1;D1:E2)

That should work. Good luck!
 
M

Max

Another, fast n easy way is to create a pivot.

With col headers inserted:
Fruit State
Apple DEAD
Apple LIVE
....

just select a cell within the table,
then create the pivot via clicking Data > Pivot table ..
In step 3, click Layout, then:
drag n drop Fruit in ROW area,
State in COLUMN area,
State in DATA area (it'll appear as Count of States).
Click OK > Finish. That's it.
Hop over next door to the pivot sheet for the results.
 
D

doyree

thank you kamil and max

but it wasn't actually the answers i was looking for.
i was looking for some formula that would

A B
apple dead
apple any condition
apple any condition
orange dead
orange any condition

then i wanted to build a condition that would give me
cell count of dead apples
cell count of live apples
and same for orange.
then i wanted to get the number for total live apples by
any condition - dead = live

hope i explained little better this time!!
please help!!

thanks!!!!
 
M

Max

Admit I'm surprised that you say this:
.. but it wasn't actually the answers i was looking for.
Click to expand...

as your "additional" clarifications below seems to point back at the same
answers that you've received. I'm out of ideas to offer you.
 
Top