count on multiple conditions

[Guest]

I'm trying to perform a count based on two columns and two conditions. I've
searched the forum and found several options but can't get any of them to
work. Will "countif" not work with date?

Here's what I'm doing:

A B
neutral 8/1/2006
positive 8/1/2006
negative 8/5/2006

I want to use a code that will search column b for a date: 8/1/2006 and
return a count of how many positives, negatives and neutrals I have within
that date.

Can anyone help?
Thanks,

Debbie

 

[Rodrigo Ferreira]

Try the function DCOUNT
Something like this:

A B
NAME DATE
neutral 8/1/2006
positive 8/1/2006
negative 8/5/2006


D E
NAME DATE
neutral 8/1/2006


DCOUNT(A1:B4;2;D1:E2)

Rodrigo Ferreira

 

[Guest]

Thank you but this didn't fix my problem.

I'm sure I wasn't clear enough.

A B
neutral 8/1/2006
positive 8/1/2006
negative 8/5/2006
Negative 8/5/2006
Positive 8/7/2006
Positive 8/7/2006
neutral 8/7/2006



positive neutral negative
1-Aug 0 DCOUNT(A1:B4;2;D1:E2) 0
2-Aug 0 0 1
3-Aug 0 0 1
4-Aug 0 1 2
5-Aug 0 1 3
6-Aug 0 0 3
7-Aug 1 0 8

You see, I want it to count how many positives I have for 8/1 and return
that number. I want it to count how many neutrals I have for 8/1 and return
that number. Same thing for negatives.
I need it to perform this action through out an entire section of
information and decipher how many per date and enter that number in the
appropriate column.
So in the 8/1 section under positive I should have a 1, under the neutral I
should have 1 and in the negative, I should have a 0.
I have no information for 8/2, 8/3 and 8/4 so those should all be 0's.
Then for 8/5 I should have 0 positives, 0 neutrals and 2 negatives.

The formula you gave me doesn't fit this scenario.

Any other ideas?

Thanks for your help,

Debbie

 

[Guest]

How about a pivot table... Place your cursor in the middle of your data and
then select Data -> Pivot Table. Now just follow the wizard (or just select
Finish as the defaults are normally correct). Place your Dates in the left
column and your Values(Positive / negative / neutral) across the top. You
also want to place your values in the middle. This will give you your counts.
If you need to you could also get fancy and group your dates to consolidate
by month, quarter or year if that was of interest to you... Reply back if
this does not make sense...

 

[Guest]

Jim, that makes sense, but I don't see this working well for my needs on this
project. I have pivot tables set to retrieve the original information from my
database. Now I need to transform this information into something more
logical that my clients can understand. Unless I just don't understand pivot
tables well enough, I think I'm looking at a lot more work than this should
be to achieve a simple count of date vs pos/neg/neut.

Thanks,

Debbie

 

[Guest]

Not too sure why you think it will not work ( I did it as a test on my end
and it came out just fine) but you could also use sumproduct if that better
suits your needs.

Creating a table similar to the result you are looking for Starting at D1

positive Negative neutral
8/1/2006 1 0 1
8/5/2006 0 2 0
8/7/2006 2 0 1

The formula in E2 would be
=SUMPRODUCT(--($D2=$B$2:$B$8), --(E$1=$A$2:$A$8))
This formula can be dragged to suit... A pivot table can create the exact
same thing though...

 

[Guest]

Jim,

You're absolutely right.
The pivot did exactly as it should.
I'm apparently on another planet today.
Thanks for your help.

Debbie