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

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


"Debbie" <(E-Mail Removed)> escreveu na mensagem
newsB331D3B-F66C-4D98-A269-(E-Mail Removed)...
> 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

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



"Rodrigo Ferreira" wrote:

> 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
>
>
> "Debbie" <(E-Mail Removed)> escreveu na mensagem
> newsB331D3B-F66C-4D98-A269-(E-Mail Removed)...
> > 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
>
>
>

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

Jim Thomlinson


"Debbie" wrote:

> 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
>
>
>
> "Rodrigo Ferreira" wrote:
>
> > 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
> >
> >
> > "Debbie" <(E-Mail Removed)> escreveu na mensagem
> > newsB331D3B-F66C-4D98-A269-(E-Mail Removed)...
> > > 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
> >
> >
> >

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

"Jim Thomlinson" wrote:

> 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...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Debbie" wrote:
>
> > 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
> >
> >
> >
> > "Rodrigo Ferreira" wrote:
> >
> > > 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
> > >
> > >
> > > "Debbie" <(E-Mail Removed)> escreveu na mensagem
> > > newsB331D3B-F66C-4D98-A269-(E-Mail Removed)...
> > > > 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
> > >
> > >
> > >

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

Jim Thomlinson


"Debbie" wrote:

> 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
>
> "Jim Thomlinson" wrote:
>
> > 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...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Debbie" wrote:
> >
> > > 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
> > >
> > >
> > >
> > > "Rodrigo Ferreira" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "Debbie" <(E-Mail Removed)> escreveu na mensagem
> > > > newsB331D3B-F66C-4D98-A269-(E-Mail Removed)...
> > > > > 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
> > > >
> > > >
> > > >

Jim,

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

Debbie

"Jim Thomlinson" wrote:

> 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...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Debbie" wrote:
>
> > 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
> >
> > "Jim Thomlinson" wrote:
> >
> > > 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...
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Debbie" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > >
> > > > "Rodrigo Ferreira" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > > "Debbie" <(E-Mail Removed)> escreveu na mensagem
> > > > > newsB331D3B-F66C-4D98-A269-(E-Mail Removed)...
> > > > > > 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
> > > > >
> > > > >
> > > > >