CountIf

I have a spread sheet and I need to count the # of times something happens
per day. Example would be I have the following data.
Date Outcome
1.1.05 Y
1.2.05 Y
1.2.05 Y
1.2.05 N
1.2.05 N
1.3.05 N
1.3.05 Y

I want the results to be as follows
Date Positive Negative
1.1.05 1 0
1.2.05 2 2
1.3.05 1 1 etc.

Michelle,

Have you tried using a pivot table?

J

"Michelle" wrote:

> I have a spread sheet and I need to count the # of times something happens
> per day. Example would be I have the following data.
> Date Outcome
> 1.1.05 Y
> 1.2.05 Y
> 1.2.05 Y
> 1.2.05 N
> 1.2.05 N
> 1.3.05 N
> 1.3.05 Y
>
> I want the results to be as follows
> Date Positive Negative
> 1.1.05 1 0
> 1.2.05 2 2
> 1.3.05 1 1 etc.

=sumproduct(--(A1:A30=DateValue("1/1/05")),--(B1:B30="Y"))

--
Regards,
Tom Ogilvy


"Michelle" <[email protected]> wrote in message
news:[email protected]...
> I have a spread sheet and I need to count the # of times something happens
> per day. Example would be I have the following data.
> Date Outcome
> 1.1.05 Y
> 1.2.05 Y
> 1.2.05 Y
> 1.2.05 N
> 1.2.05 N
> 1.3.05 N
> 1.3.05 Y
>
> I want the results to be as follows
> Date Positive Negative
> 1.1.05 1 0
> 1.2.05 2 2
> 1.3.05 1 1 etc.

No

"James M" wrote:

> Michelle,
>
> Have you tried using a pivot table?
>
> J
>
> "Michelle" wrote:
>
> > I have a spread sheet and I need to count the # of times something happens
> > per day. Example would be I have the following data.
> > Date Outcome
> > 1.1.05 Y
> > 1.2.05 Y
> > 1.2.05 Y
> > 1.2.05 N
> > 1.2.05 N
> > 1.3.05 N
> > 1.3.05 Y
> >
> > I want the results to be as follows
> > Date Positive Negative
> > 1.1.05 1 0
> > 1.2.05 2 2
> > 1.3.05 1 1 etc.

I tried this and it does not seem to return the # of times that Y hapens on
the given date.

"Tom Ogilvy" wrote:

> =sumproduct(--(A1:A30=DateValue("1/1/05")),--(B1:B30="Y"))
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Michelle" <[email protected]> wrote in message
> news:[email protected]...
> > I have a spread sheet and I need to count the # of times something happens
> > per day. Example would be I have the following data.
> > Date Outcome
> > 1.1.05 Y
> > 1.2.05 Y
> > 1.2.05 Y
> > 1.2.05 N
> > 1.2.05 N
> > 1.3.05 N
> > 1.3.05 Y
> >
> > I want the results to be as follows
> > Date Positive Negative
> > 1.1.05 1 0
> > 1.2.05 2 2
> > 1.3.05 1 1 etc.
>
>
>

If applied correctly, it will.

--
Regards,
Tom Ogilvy


"Michelle" <[email protected]> wrote in message
news:[email protected]...
> I tried this and it does not seem to return the # of times that Y hapens
on
> the given date.
>
> "Tom Ogilvy" wrote:
>
> > =sumproduct(--(A1:A30=DateValue("1/1/05")),--(B1:B30="Y"))
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Michelle" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a spread sheet and I need to count the # of times something
happens
> > > per day. Example would be I have the following data.
> > > Date Outcome
> > > 1.1.05 Y
> > > 1.2.05 Y
> > > 1.2.05 Y
> > > 1.2.05 N
> > > 1.2.05 N
> > > 1.3.05 N
> > > 1.3.05 Y
> > >
> > > I want the results to be as follows
> > > Date Positive Negative
> > > 1.1.05 1 0
> > > 1.2.05 2 2
> > > 1.3.05 1 1 etc.
> >
> >
> >

The formula of sumproduct worked well!

Try to look at your date fomat Michelle to have exact value in between quotation:

=sumproduct(--(A1:A30=DateValue("1/1/05")),--(B1:B30="Y"))

Regards
Rock

Then maybe you should.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michelle" <[email protected]> wrote in message
news:[email protected]...
> No
>
> "James M" wrote:
>
> > Michelle,
> >
> > Have you tried using a pivot table?
> >
> > J
> >
> > "Michelle" wrote:
> >
> > > I have a spread sheet and I need to count the # of times something
happens
> > > per day. Example would be I have the following data.
> > > Date Outcome
> > > 1.1.05 Y
> > > 1.2.05 Y
> > > 1.2.05 Y
> > > 1.2.05 N
> > > 1.2.05 N
> > > 1.3.05 N
> > > 1.3.05 Y
> > >
> > > I want the results to be as follows
> > > Date Positive Negative
> > > 1.1.05 1 0
> > > 1.2.05 2 2
> > > 1.3.05 1 1 etc.

Not to sound dumb but are the -- spaces?

"Rock" wrote:

>
> The formula of sumproduct worked well!
>
> Try to look at your date fomat Michelle to have exact value in between
> quotation:
>
> =sumproduct(--(A1:A30=DateValue("1/1/05")),--(B1:B30="Y"))
>
> Regards
> Rock
>
>
> --
> Rock
> ------------------------------------------------------------------------
> Rock's Profile: http://www.excelforum.com/member.php...o&userid=29723
> View this thread: http://www.excelforum.com/showthread...hreadid=494582
>
>

In column A you should type exact:
1.1.05
1.2.05
1.2.05
1.2.05
1.2.05
1.3.05
1.3.05

In column B you should type exact:
Y
N
...

1- Select cell C1,
2- you go to function to select sumproduct function,
3- "Function Aguments" will appear
4- then you type exactly --(A1:A30=DateValue("1.1.05") in agument1 and so on.

It will work.
Note: it doesn't work when you just copy the formular and put to cell C1. I don't know why.

It is for today and Good luck!
Rock

=sumproduct(--(A1:A30=DateValue("1.1.05")),--(B1:B30="Y"))


Not to sound dumb but are the -- spaces?

"Rock" wrote:

>
> The formula of sumproduct worked well!
>
> Try to look at your date fomat Michelle to have exact value in between
> quotation:
>
> =sumproduct(--(A1:A30=DateValue("1/1/05")),--(B1:B30="Y"))
>
> Regards
> Rock

=sumproduct() likes to work with numbers.

The -- converts True's and False's to 1's and 0's.

-True = -1
--True = +1


Michelle wrote:
>
> Not to sound dumb but are the -- spaces?
>
> "Rock" wrote:
>
> >
> > The formula of sumproduct worked well!
> >
> > Try to look at your date fomat Michelle to have exact value in between
> > quotation:
> >
> > =sumproduct(--(A1:A30=DateValue("1/1/05")),--(B1:B30="Y"))
> >
> > Regards
> > Rock
> >
> >
> > --
> > Rock
> > ------------------------------------------------------------------------
> > Rock's Profile: http://www.excelforum.com/member.php...o&userid=29723
> > View this thread: http://www.excelforum.com/showthread...hreadid=494582
> >
> >

--

Dave Peterson

If I would change my Y & N to 1 and 0 could I do a sumif?
This seems like a lot of extra work as I need to do this for every day of
the year for both they Y and the N it would not be as bad if you could do the
copy thing.

"Rock" wrote:

>
> In column A you should type exact:
> 1.1.05
> 1.2.05
> 1.2.05
> 1.2.05
> 1.2.05
> 1.3.05
> 1.3.05
>
> In column B you should type exact:
> Y
> N
> ...
>
> 1- Select cell C1,
> 2- you go to function to select sumproduct function,
> 3- "Function Aguments" will appear
> 4- then you type exactly --(A1:A30=DateValue("1.1.05") in agument1 and
> so on.
>
> It will work.
> Note: it doesn't work when you just copy the formular and put to cell
> C1. I don't know why.
>
> It is for today and Good luck!
> Rock
>
> =sumproduct(--(A1:A30=DateValue("1.1.05")),--(B1:B30="Y"))
>
>
> Not to sound dumb but are the -- spaces?
>
> "Rock" wrote:
>
> >
> > The formula of sumproduct worked well!
> >
> > Try to look at your date fomat Michelle to have exact value in
> between
> > quotation:
> >
> > =sumproduct(--(A1:A30=DateValue("1/1/05")),--(B1:B30="Y"))
> >
> > Regards
> > Rock
>
>
> --
> Rock
> ------------------------------------------------------------------------
> Rock's Profile: http://www.excelforum.com/member.php...o&userid=29723
> View this thread: http://www.excelforum.com/showthread...hreadid=494582
>
>

You are summing on two criteria - date and type of response. Sumif only
handles a single condition. As long as this will me a manual operation each
day, you might try the pivot table as suggested. It is fast and easy.

--
Regards,
Tom Ogilvy

"Michelle" <[email protected]> wrote in message
news:[email protected]...
> If I would change my Y & N to 1 and 0 could I do a sumif?
> This seems like a lot of extra work as I need to do this for every day of
> the year for both they Y and the N it would not be as bad if you could do
the
> copy thing.
>
> "Rock" wrote:
>
> >
> > In column A you should type exact:
> > 1.1.05
> > 1.2.05
> > 1.2.05
> > 1.2.05
> > 1.2.05
> > 1.3.05
> > 1.3.05
> >
> > In column B you should type exact:
> > Y
> > N
> > ...
> >
> > 1- Select cell C1,
> > 2- you go to function to select sumproduct function,
> > 3- "Function Aguments" will appear
> > 4- then you type exactly --(A1:A30=DateValue("1.1.05") in agument1 and
> > so on.
> >
> > It will work.
> > Note: it doesn't work when you just copy the formular and put to cell
> > C1. I don't know why.
> >
> > It is for today and Good luck!
> > Rock
> >
> > =sumproduct(--(A1:A30=DateValue("1.1.05")),--(B1:B30="Y"))
> >
> >
> > Not to sound dumb but are the -- spaces?
> >
> > "Rock" wrote:
> >
> > >
> > > The formula of sumproduct worked well!
> > >
> > > Try to look at your date fomat Michelle to have exact value in
> > between
> > > quotation:
> > >
> > > =sumproduct(--(A1:A30=DateValue("1/1/05")),--(B1:B30="Y"))
> > >
> > > Regards
> > > Rock
> >
> >
> > --
> > Rock
> > ------------------------------------------------------------------------
> > Rock's Profile:
http://www.excelforum.com/member.php...o&userid=29723
> > View this thread:
http://www.excelforum.com/showthread...hreadid=494582
> >
> >