IF STATEMEMNTS

I once knew this... how can I nest a COUNTIF statement within an IF
statement? As in, If A:A = 9/1/2005 count B:B if = "A"

(I'm trying to count the number of As, Bc, Cs, etc based on a large
worksheet by months.

Thanks!

Assume your dates are in the range A1:A5 and column B range is from B1:B5,
this formula will work:

=SUMPRODUCT(--(MONTH(A1:A5)=9),--(B1:B5="A"))

Hope that helps,
--
Regards,

David Billigmeier



"Judy" wrote:

> I once knew this... how can I nest a COUNTIF statement within an IF
> statement? As in, If A:A = 9/1/2005 count B:B if = "A"
>
> (I'm trying to count the number of As, Bc, Cs, etc based on a large
> worksheet by months.
>
> Thanks!

Assume your dates are in the range A1:A5 and column B range is from B1:B5,
this formula will work:

=SUMPRODUCT(--(MONTH(A1:A5)=9),--(B1:B5="A"))

Hope that helps,
--
Regards,

David Billigmeier



"Judy" wrote:

> I once knew this... how can I nest a COUNTIF statement within an IF
> statement? As in, If A:A = 9/1/2005 count B:B if = "A"
>
> (I'm trying to count the number of As, Bc, Cs, etc based on a large
> worksheet by months.
>
> Thanks!

Assume your dates are in the range A1:A5 and column B range is from B1:B5,
this formula will work:

=SUMPRODUCT(--(MONTH(A1:A5)=9),--(B1:B5="A"))

Hope that helps,
--
Regards,

David Billigmeier



"Judy" wrote:

> I once knew this... how can I nest a COUNTIF statement within an IF
> statement? As in, If A:A = 9/1/2005 count B:B if = "A"
>
> (I'm trying to count the number of As, Bc, Cs, etc based on a large
> worksheet by months.
>
> Thanks!

Assume your dates are in the range A1:A5 and column B range is from B1:B5,
this formula will work:

=SUMPRODUCT(--(MONTH(A1:A5)=9),--(B1:B5="A"))

Hope that helps,
--
Regards,

David Billigmeier



"Judy" wrote:

> I once knew this... how can I nest a COUNTIF statement within an IF
> statement? As in, If A:A = 9/1/2005 count B:B if = "A"
>
> (I'm trying to count the number of As, Bc, Cs, etc based on a large
> worksheet by months.
>
> Thanks!

Assume your dates are in the range A1:A5 and column B range is from B1:B5,
this formula will work:

=SUMPRODUCT(--(MONTH(A1:A5)=9),--(B1:B5="A"))

Hope that helps,
--
Regards,

David Billigmeier



"Judy" wrote:

> I once knew this... how can I nest a COUNTIF statement within an IF
> statement? As in, If A:A = 9/1/2005 count B:B if = "A"
>
> (I'm trying to count the number of As, Bc, Cs, etc based on a large
> worksheet by months.
>
> Thanks!

One way:

=SUMPRODUCT(--(A1:A1000=DATE(2005,9,1)),--(B1:B1000="A"))

Or if months:

=SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))

In article <[email protected]>,
"Judy" <[email protected]> wrote:

> I once knew this... how can I nest a COUNTIF statement within an IF
> statement? As in, If A:A = 9/1/2005 count B:B if = "A"
>
> (I'm trying to count the number of As, Bc, Cs, etc based on a large
> worksheet by months.
>
> Thanks!

One way:

=SUMPRODUCT(--(A1:A1000=DATE(2005,9,1)),--(B1:B1000="A"))

Or if months:

=SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))

In article <[email protected]>,
"Judy" <[email protected]> wrote:

> I once knew this... how can I nest a COUNTIF statement within an IF
> statement? As in, If A:A = 9/1/2005 count B:B if = "A"
>
> (I'm trying to count the number of As, Bc, Cs, etc based on a large
> worksheet by months.
>
> Thanks!

thanks to you both. This works on the same worksheet, but not a different
worksheet I'm using for a summary... why would that be?

"JE McGimpsey" wrote:

> One way:
>
> =SUMPRODUCT(--(A1:A1000=DATE(2005,9,1)),--(B1:B1000="A"))
>
> Or if months:
>
> =SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))
>
> In article <[email protected]>,
> "Judy" <[email protected]> wrote:
>
> > I once knew this... how can I nest a COUNTIF statement within an IF
> > statement? As in, If A:A = 9/1/2005 count B:B if = "A"
> >
> > (I'm trying to count the number of As, Bc, Cs, etc based on a large
> > worksheet by months.
> >
> > Thanks!
>

Probably because the changes you made to the formula were incorrect.

What do you mean when you say it didn't work? Did you get an error? an
incorrect result? a crash? no result?


In article <[email protected]>,
"Judy" <[email protected]> wrote:

> thanks to you both. This works on the same worksheet, but not a different
> worksheet I'm using for a summary... why would that be?

Probably because the changes you made to the formula were incorrect.

What do you mean when you say it didn't work? Did you get an error? an
incorrect result? a crash? no result?


In article <[email protected]>,
"Judy" <[email protected]> wrote:

> thanks to you both. This works on the same worksheet, but not a different
> worksheet I'm using for a summary... why would that be?

I changed it as follows:

Your example:
=SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))

=SUMPRODUCT(--(MONTH('2005'!A:A)=9),--('2005'!J:J="A"))
I get #NUM! with this one.

=SUMPRODUCT(--(MONTH('2005'!A1:A2000)=9),--('2005'!J1:J2000="A"))
I get #VALUE! with this one

2005 is the name of the worksheet with the data. Thanks for your help.


"JE McGimpsey" wrote:

> Probably because the changes you made to the formula were incorrect.
>
> What do you mean when you say it didn't work? Did you get an error? an
> incorrect result? a crash? no result?
>
>
> In article <[email protected]>,
> "Judy" <[email protected]> wrote:
>
> > thanks to you both. This works on the same worksheet, but not a different
> > worksheet I'm using for a summary... why would that be?
>

Your #NUM! error comes from the fact that you can't use entire columns
(or rows) in array formulae - and SUMPRODUCT is an array formula even if
you don't need to use Control-Shift-Enter.

The #VALUE! error works for me. Check to see that you don't have a cell
with #VALUE! in A1:A2000 or J1:J2000.



In article <[email protected]>,
"Judy" <[email protected]> wrote:

> I changed it as follows:
>
> Your example:
> =SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))
>
> =SUMPRODUCT(--(MONTH('2005'!A:A)=9),--('2005'!J:J="A"))
> I get #NUM! with this one.
>
> =SUMPRODUCT(--(MONTH('2005'!A1:A2000)=9),--('2005'!J1:J2000="A"))
> I get #VALUE! with this one
>
> 2005 is the name of the worksheet with the data. Thanks for your help.
>
>
> "JE McGimpsey" wrote:
>
> > Probably because the changes you made to the formula were incorrect.
> >
> > What do you mean when you say it didn't work? Did you get an error? an
> > incorrect result? a crash? no result?
> >
> >
> > In article <[email protected]>,
> > "Judy" <[email protected]> wrote:
> >
> > > thanks to you both. This works on the same worksheet, but not a
> > > different
> > > worksheet I'm using for a summary... why would that be?
> >

Your #NUM! error comes from the fact that you can't use entire columns
(or rows) in array formulae - and SUMPRODUCT is an array formula even if
you don't need to use Control-Shift-Enter.

The #VALUE! error works for me. Check to see that you don't have a cell
with #VALUE! in A1:A2000 or J1:J2000.



In article <[email protected]>,
"Judy" <[email protected]> wrote:

> I changed it as follows:
>
> Your example:
> =SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))
>
> =SUMPRODUCT(--(MONTH('2005'!A:A)=9),--('2005'!J:J="A"))
> I get #NUM! with this one.
>
> =SUMPRODUCT(--(MONTH('2005'!A1:A2000)=9),--('2005'!J1:J2000="A"))
> I get #VALUE! with this one
>
> 2005 is the name of the worksheet with the data. Thanks for your help.
>
>
> "JE McGimpsey" wrote:
>
> > Probably because the changes you made to the formula were incorrect.
> >
> > What do you mean when you say it didn't work? Did you get an error? an
> > incorrect result? a crash? no result?
> >
> >
> > In article <[email protected]>,
> > "Judy" <[email protected]> wrote:
> >
> > > thanks to you both. This works on the same worksheet, but not a
> > > different
> > > worksheet I'm using for a summary... why would that be?
> >

Your #NUM! error comes from the fact that you can't use entire columns
(or rows) in array formulae - and SUMPRODUCT is an array formula even if
you don't need to use Control-Shift-Enter.

The #VALUE! error works for me. Check to see that you don't have a cell
with #VALUE! in A1:A2000 or J1:J2000.



In article <[email protected]>,
"Judy" <[email protected]> wrote:

> I changed it as follows:
>
> Your example:
> =SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))
>
> =SUMPRODUCT(--(MONTH('2005'!A:A)=9),--('2005'!J:J="A"))
> I get #NUM! with this one.
>
> =SUMPRODUCT(--(MONTH('2005'!A1:A2000)=9),--('2005'!J1:J2000="A"))
> I get #VALUE! with this one
>
> 2005 is the name of the worksheet with the data. Thanks for your help.
>
>
> "JE McGimpsey" wrote:
>
> > Probably because the changes you made to the formula were incorrect.
> >
> > What do you mean when you say it didn't work? Did you get an error? an
> > incorrect result? a crash? no result?
> >
> >
> > In article <[email protected]>,
> > "Judy" <[email protected]> wrote:
> >
> > > thanks to you both. This works on the same worksheet, but not a
> > > different
> > > worksheet I'm using for a summary... why would that be?
> >

Your #NUM! error comes from the fact that you can't use entire columns
(or rows) in array formulae - and SUMPRODUCT is an array formula even if
you don't need to use Control-Shift-Enter.

The #VALUE! error works for me. Check to see that you don't have a cell
with #VALUE! in A1:A2000 or J1:J2000.



In article <[email protected]>,
"Judy" <[email protected]> wrote:

> I changed it as follows:
>
> Your example:
> =SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))
>
> =SUMPRODUCT(--(MONTH('2005'!A:A)=9),--('2005'!J:J="A"))
> I get #NUM! with this one.
>
> =SUMPRODUCT(--(MONTH('2005'!A1:A2000)=9),--('2005'!J1:J2000="A"))
> I get #VALUE! with this one
>
> 2005 is the name of the worksheet with the data. Thanks for your help.
>
>
> "JE McGimpsey" wrote:
>
> > Probably because the changes you made to the formula were incorrect.
> >
> > What do you mean when you say it didn't work? Did you get an error? an
> > incorrect result? a crash? no result?
> >
> >
> > In article <[email protected]>,
> > "Judy" <[email protected]> wrote:
> >
> > > thanks to you both. This works on the same worksheet, but not a
> > > different
> > > worksheet I'm using for a summary... why would that be?
> >

Your #NUM! error comes from the fact that you can't use entire columns
(or rows) in array formulae - and SUMPRODUCT is an array formula even if
you don't need to use Control-Shift-Enter.

The #VALUE! error works for me. Check to see that you don't have a cell
with #VALUE! in A1:A2000 or J1:J2000.



In article <[email protected]>,
"Judy" <[email protected]> wrote:

> I changed it as follows:
>
> Your example:
> =SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))
>
> =SUMPRODUCT(--(MONTH('2005'!A:A)=9),--('2005'!J:J="A"))
> I get #NUM! with this one.
>
> =SUMPRODUCT(--(MONTH('2005'!A1:A2000)=9),--('2005'!J1:J2000="A"))
> I get #VALUE! with this one
>
> 2005 is the name of the worksheet with the data. Thanks for your help.
>
>
> "JE McGimpsey" wrote:
>
> > Probably because the changes you made to the formula were incorrect.
> >
> > What do you mean when you say it didn't work? Did you get an error? an
> > incorrect result? a crash? no result?
> >
> >
> > In article <[email protected]>,
> > "Judy" <[email protected]> wrote:
> >
> > > thanks to you both. This works on the same worksheet, but not a
> > > different
> > > worksheet I'm using for a summary... why would that be?
> >

I changed it as follows:

Your example:
=SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))

=SUMPRODUCT(--(MONTH('2005'!A:A)=9),--('2005'!J:J="A"))
I get #NUM! with this one.

=SUMPRODUCT(--(MONTH('2005'!A1:A2000)=9),--('2005'!J1:J2000="A"))
I get #VALUE! with this one

2005 is the name of the worksheet with the data. Thanks for your help.


"JE McGimpsey" wrote:

> Probably because the changes you made to the formula were incorrect.
>
> What do you mean when you say it didn't work? Did you get an error? an
> incorrect result? a crash? no result?
>
>
> In article <[email protected]>,
> "Judy" <[email protected]> wrote:
>
> > thanks to you both. This works on the same worksheet, but not a different
> > worksheet I'm using for a summary... why would that be?
>

I changed it as follows:

Your example:
=SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))

=SUMPRODUCT(--(MONTH('2005'!A:A)=9),--('2005'!J:J="A"))
I get #NUM! with this one.

=SUMPRODUCT(--(MONTH('2005'!A1:A2000)=9),--('2005'!J1:J2000="A"))
I get #VALUE! with this one

2005 is the name of the worksheet with the data. Thanks for your help.


"JE McGimpsey" wrote:

> Probably because the changes you made to the formula were incorrect.
>
> What do you mean when you say it didn't work? Did you get an error? an
> incorrect result? a crash? no result?
>
>
> In article <[email protected]>,
> "Judy" <[email protected]> wrote:
>
> > thanks to you both. This works on the same worksheet, but not a different
> > worksheet I'm using for a summary... why would that be?
>

I changed it as follows:

Your example:
=SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))

=SUMPRODUCT(--(MONTH('2005'!A:A)=9),--('2005'!J:J="A"))
I get #NUM! with this one.

=SUMPRODUCT(--(MONTH('2005'!A1:A2000)=9),--('2005'!J1:J2000="A"))
I get #VALUE! with this one

2005 is the name of the worksheet with the data. Thanks for your help.


"JE McGimpsey" wrote:

> Probably because the changes you made to the formula were incorrect.
>
> What do you mean when you say it didn't work? Did you get an error? an
> incorrect result? a crash? no result?
>
>
> In article <[email protected]>,
> "Judy" <[email protected]> wrote:
>
> > thanks to you both. This works on the same worksheet, but not a different
> > worksheet I'm using for a summary... why would that be?
>

I changed it as follows:

Your example:
=SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))

=SUMPRODUCT(--(MONTH('2005'!A:A)=9),--('2005'!J:J="A"))
I get #NUM! with this one.

=SUMPRODUCT(--(MONTH('2005'!A1:A2000)=9),--('2005'!J1:J2000="A"))
I get #VALUE! with this one

2005 is the name of the worksheet with the data. Thanks for your help.


"JE McGimpsey" wrote:

> Probably because the changes you made to the formula were incorrect.
>
> What do you mean when you say it didn't work? Did you get an error? an
> incorrect result? a crash? no result?
>
>
> In article <[email protected]>,
> "Judy" <[email protected]> wrote:
>
> > thanks to you both. This works on the same worksheet, but not a different
> > worksheet I'm using for a summary... why would that be?
>

Probably because the changes you made to the formula were incorrect.

What do you mean when you say it didn't work? Did you get an error? an
incorrect result? a crash? no result?


In article <[email protected]>,
"Judy" <[email protected]> wrote:

> thanks to you both. This works on the same worksheet, but not a different
> worksheet I'm using for a summary... why would that be?

Probably because the changes you made to the formula were incorrect.

What do you mean when you say it didn't work? Did you get an error? an
incorrect result? a crash? no result?


In article <[email protected]>,
"Judy" <[email protected]> wrote:

> thanks to you both. This works on the same worksheet, but not a different
> worksheet I'm using for a summary... why would that be?

Probably because the changes you made to the formula were incorrect.

What do you mean when you say it didn't work? Did you get an error? an
incorrect result? a crash? no result?


In article <[email protected]>,
"Judy" <[email protected]> wrote:

> thanks to you both. This works on the same worksheet, but not a different
> worksheet I'm using for a summary... why would that be?

thanks to you both. This works on the same worksheet, but not a different
worksheet I'm using for a summary... why would that be?

"JE McGimpsey" wrote:

> One way:
>
> =SUMPRODUCT(--(A1:A1000=DATE(2005,9,1)),--(B1:B1000="A"))
>
> Or if months:
>
> =SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))
>
> In article <[email protected]>,
> "Judy" <[email protected]> wrote:
>
> > I once knew this... how can I nest a COUNTIF statement within an IF
> > statement? As in, If A:A = 9/1/2005 count B:B if = "A"
> >
> > (I'm trying to count the number of As, Bc, Cs, etc based on a large
> > worksheet by months.
> >
> > Thanks!
>

thanks to you both. This works on the same worksheet, but not a different
worksheet I'm using for a summary... why would that be?

"JE McGimpsey" wrote:

> One way:
>
> =SUMPRODUCT(--(A1:A1000=DATE(2005,9,1)),--(B1:B1000="A"))
>
> Or if months:
>
> =SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))
>
> In article <[email protected]>,
> "Judy" <[email protected]> wrote:
>
> > I once knew this... how can I nest a COUNTIF statement within an IF
> > statement? As in, If A:A = 9/1/2005 count B:B if = "A"
> >
> > (I'm trying to count the number of As, Bc, Cs, etc based on a large
> > worksheet by months.
> >
> > Thanks!
>

thanks to you both. This works on the same worksheet, but not a different
worksheet I'm using for a summary... why would that be?

"JE McGimpsey" wrote:

> One way:
>
> =SUMPRODUCT(--(A1:A1000=DATE(2005,9,1)),--(B1:B1000="A"))
>
> Or if months:
>
> =SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))
>
> In article <[email protected]>,
> "Judy" <[email protected]> wrote:
>
> > I once knew this... how can I nest a COUNTIF statement within an IF
> > statement? As in, If A:A = 9/1/2005 count B:B if = "A"
> >
> > (I'm trying to count the number of As, Bc, Cs, etc based on a large
> > worksheet by months.
> >
> > Thanks!
>

thanks to you both. This works on the same worksheet, but not a different
worksheet I'm using for a summary... why would that be?

"JE McGimpsey" wrote:

> One way:
>
> =SUMPRODUCT(--(A1:A1000=DATE(2005,9,1)),--(B1:B1000="A"))
>
> Or if months:
>
> =SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))
>
> In article <[email protected]>,
> "Judy" <[email protected]> wrote:
>
> > I once knew this... how can I nest a COUNTIF statement within an IF
> > statement? As in, If A:A = 9/1/2005 count B:B if = "A"
> >
> > (I'm trying to count the number of As, Bc, Cs, etc based on a large
> > worksheet by months.
> >
> > Thanks!
>

One way:

=SUMPRODUCT(--(A1:A1000=DATE(2005,9,1)),--(B1:B1000="A"))

Or if months:

=SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))

In article <[email protected]>,
"Judy" <[email protected]> wrote:

> I once knew this... how can I nest a COUNTIF statement within an IF
> statement? As in, If A:A = 9/1/2005 count B:B if = "A"
>
> (I'm trying to count the number of As, Bc, Cs, etc based on a large
> worksheet by months.
>
> Thanks!

One way:

=SUMPRODUCT(--(A1:A1000=DATE(2005,9,1)),--(B1:B1000="A"))

Or if months:

=SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))

In article <[email protected]>,
"Judy" <[email protected]> wrote:

> I once knew this... how can I nest a COUNTIF statement within an IF
> statement? As in, If A:A = 9/1/2005 count B:B if = "A"
>
> (I'm trying to count the number of As, Bc, Cs, etc based on a large
> worksheet by months.
>
> Thanks!

One way:

=SUMPRODUCT(--(A1:A1000=DATE(2005,9,1)),--(B1:B1000="A"))

Or if months:

=SUMPRODUCT(--(MONTH(A1:A1000)=9),(B1:B1000="A"))

In article <[email protected]>,
"Judy" <[email protected]> wrote:

> I once knew this... how can I nest a COUNTIF statement within an IF
> statement? As in, If A:A = 9/1/2005 count B:B if = "A"
>
> (I'm trying to count the number of As, Bc, Cs, etc based on a large
> worksheet by months.
>
> Thanks!