IF STATEMEMNTS

Judy wrote...
>THANKS everyone. I did get my formula to work with one exception. The
>column this fomula refers to (J) is also a formula based column and the
>formula is filled quite a ways down the worksheet, meaning there are sever=
al
>rows where #N/A shows until data is entered. If I make the ranges for
>columns A and J only the populated rows, it works. If I use A:A and J:J, =
it
>doesn't... I don't suppose there's a fix for that!
..=2E.

You can't do what you want using entire column references like A:A and
J:J. If you keep using them, you'll keep getting errors. JE already
pointed this out to you. If error values as results are fine with you,
go on using entire column references. On the other hand, if you want
numeric results, YOU CAN'T USE ENTIRE COLUMN REFERENCES.

Do you really have data in ALL rows in columns A and J? If not, you
could use the array formula

=3DSUM(IF(ISNUMBER(MONTH(A1:A1000)),(MONTH(A1:A1000)=AD=3D9)*(B1:B1000=3D"A=
")))

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?

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?
> >

"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> Your #NUM! error comes from the fact that you can't use entire columns
> (or rows) in array formulae

Are you sure? Array fomulas for whole rows seem to work for me.

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"

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!
>

JE McGimpsey wrote...
>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.

You're wrong about entire rows. They work just fine. Might almost lead
one to believe Excel's programmers used unsigned short integers to
index arrays internally without realizing that it'd mean they couldn't
handle 65,536 entries in any dimension.

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

Another possibility would be feeding invalid date serial numbers to
MONTH. Any nonnumeric values or numeric values outside 0 to
DATE(9999,12,31) will cause MONTH to return #VALUE!.

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

> You're wrong about entire rows. They work just fine. Might almost lead
> one to believe Excel's programmers used unsigned short integers to
> index arrays internally without realizing that it'd mean they couldn't
> handle 65,536 entries in any dimension.

Thanks for the correction, Harlan - I never use entire rows, and so I
forget the reason. Perhaps one more time will make it stick.

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!

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

> Are you sure? Array fomulas for whole rows seem to work for me.

No, I was incorrect.

THANKS everyone. I did get my formula to work with one exception. The
column this fomula refers to (J) is also a formula based column and the
formula is filled quite a ways down the worksheet, meaning there are several
rows where #N/A shows until data is entered. If I make the ranges for
columns A and J only the populated rows, it works. If I use A:A and J:J, it
doesn't... I don't suppose there's a fix for that!

Thanks again - appreciated!

"JE McGimpsey" wrote:

> In article <[email protected]>,
> "Harlan Grove" <[email protected]> wrote:
>
> > You're wrong about entire rows. They work just fine. Might almost lead
> > one to believe Excel's programmers used unsigned short integers to
> > index arrays internally without realizing that it'd mean they couldn't
> > handle 65,536 entries in any dimension.
>
> Thanks for the correction, Harlan - I never use entire rows, and so I
> forget the reason. Perhaps one more time will make it stick.
>

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!

The fix is to correct the formulas that produce #N/A - in a
well-designed worksheet, you shouldn't get errors for expected results.

For instance, if your formula is a VLOOKUP like

=VLOOKUP(A1234, Sheet2!A:B, 2, FALSE)

then you could use

=IF(ISBLANK(A1234), "", VLOOKUP(A1234,Sheet2!A:B, 2, FALSE))



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

> I don't suppose there's a fix for that!

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

> Are you sure? Array fomulas for whole rows seem to work for me.

No, I was incorrect.

THANKS everyone. I did get my formula to work with one exception. The
column this fomula refers to (J) is also a formula based column and the
formula is filled quite a ways down the worksheet, meaning there are several
rows where #N/A shows until data is entered. If I make the ranges for
columns A and J only the populated rows, it works. If I use A:A and J:J, it
doesn't... I don't suppose there's a fix for that!

Thanks again - appreciated!

"JE McGimpsey" wrote:

> In article <[email protected]>,
> "Harlan Grove" <[email protected]> wrote:
>
> > You're wrong about entire rows. They work just fine. Might almost lead
> > one to believe Excel's programmers used unsigned short integers to
> > index arrays internally without realizing that it'd mean they couldn't
> > handle 65,536 entries in any dimension.
>
> Thanks for the correction, Harlan - I never use entire rows, and so I
> forget the reason. Perhaps one more time will make it stick.
>

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

> You're wrong about entire rows. They work just fine. Might almost lead
> one to believe Excel's programmers used unsigned short integers to
> index arrays internally without realizing that it'd mean they couldn't
> handle 65,536 entries in any dimension.

Thanks for the correction, Harlan - I never use entire rows, and so I
forget the reason. Perhaps one more time will make it stick.

JE McGimpsey wrote...
>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.

You're wrong about entire rows. They work just fine. Might almost lead
one to believe Excel's programmers used unsigned short integers to
index arrays internally without realizing that it'd mean they couldn't
handle 65,536 entries in any dimension.

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

Another possibility would be feeding invalid date serial numbers to
MONTH. Any nonnumeric values or numeric values outside 0 to
DATE(9999,12,31) will cause MONTH to return #VALUE!.

The fix is to correct the formulas that produce #N/A - in a
well-designed worksheet, you shouldn't get errors for expected results.

For instance, if your formula is a VLOOKUP like

=VLOOKUP(A1234, Sheet2!A:B, 2, FALSE)

then you could use

=IF(ISBLANK(A1234), "", VLOOKUP(A1234,Sheet2!A:B, 2, FALSE))



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

> I don't suppose there's a fix for that!

Judy wrote...
>THANKS everyone. I did get my formula to work with one exception. The
>column this fomula refers to (J) is also a formula based column and the
>formula is filled quite a ways down the worksheet, meaning there are sever=
al
>rows where #N/A shows until data is entered. If I make the ranges for
>columns A and J only the populated rows, it works. If I use A:A and J:J, =
it
>doesn't... I don't suppose there's a fix for that!
..=2E.

You can't do what you want using entire column references like A:A and
J:J. If you keep using them, you'll keep getting errors. JE already
pointed this out to you. If error values as results are fine with you,
go on using entire column references. On the other hand, if you want
numeric results, YOU CAN'T USE ENTIRE COLUMN REFERENCES.

Do you really have data in ALL rows in columns A and J? If not, you
could use the array formula

=3DSUM(IF(ISNUMBER(MONTH(A1:A1000)),(MONTH(A1:A1000)=AD=3D9)*(B1:B1000=3D"A=
")))

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?
> >

"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> Your #NUM! error comes from the fact that you can't use entire columns
> (or rows) in array formulae

Are you sure? Array fomulas for whole rows seem to work for me.

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"

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?
>

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?

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!
>

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!

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

> Are you sure? Array fomulas for whole rows seem to work for me.

No, I was incorrect.

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

> You're wrong about entire rows. They work just fine. Might almost lead
> one to believe Excel's programmers used unsigned short integers to
> index arrays internally without realizing that it'd mean they couldn't
> handle 65,536 entries in any dimension.

Thanks for the correction, Harlan - I never use entire rows, and so I
forget the reason. Perhaps one more time will make it stick.

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 everyone. I did get my formula to work with one exception. The
column this fomula refers to (J) is also a formula based column and the
formula is filled quite a ways down the worksheet, meaning there are several
rows where #N/A shows until data is entered. If I make the ranges for
columns A and J only the populated rows, it works. If I use A:A and J:J, it
doesn't... I don't suppose there's a fix for that!

Thanks again - appreciated!

"JE McGimpsey" wrote:

> In article <[email protected]>,
> "Harlan Grove" <[email protected]> wrote:
>
> > You're wrong about entire rows. They work just fine. Might almost lead
> > one to believe Excel's programmers used unsigned short integers to
> > index arrays internally without realizing that it'd mean they couldn't
> > handle 65,536 entries in any dimension.
>
> Thanks for the correction, Harlan - I never use entire rows, and so I
> forget the reason. Perhaps one more time will make it stick.
>

JE McGimpsey wrote...
>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.

You're wrong about entire rows. They work just fine. Might almost lead
one to believe Excel's programmers used unsigned short integers to
index arrays internally without realizing that it'd mean they couldn't
handle 65,536 entries in any dimension.

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

Another possibility would be feeding invalid date serial numbers to
MONTH. Any nonnumeric values or numeric values outside 0 to
DATE(9999,12,31) will cause MONTH to return #VALUE!.

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!

"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> Your #NUM! error comes from the fact that you can't use entire columns
> (or rows) in array formulae

Are you sure? Array fomulas for whole rows seem to work for me.

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"

The fix is to correct the formulas that produce #N/A - in a
well-designed worksheet, you shouldn't get errors for expected results.

For instance, if your formula is a VLOOKUP like

=VLOOKUP(A1234, Sheet2!A:B, 2, FALSE)

then you could use

=IF(ISBLANK(A1234), "", VLOOKUP(A1234,Sheet2!A:B, 2, FALSE))



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

> I don't suppose there's a fix for that!

Judy wrote...
>THANKS everyone. I did get my formula to work with one exception. The
>column this fomula refers to (J) is also a formula based column and the
>formula is filled quite a ways down the worksheet, meaning there are sever=
al
>rows where #N/A shows until data is entered. If I make the ranges for
>columns A and J only the populated rows, it works. If I use A:A and J:J, =
it
>doesn't... I don't suppose there's a fix for that!
..=2E.

You can't do what you want using entire column references like A:A and
J:J. If you keep using them, you'll keep getting errors. JE already
pointed this out to you. If error values as results are fine with you,
go on using entire column references. On the other hand, if you want
numeric results, YOU CAN'T USE ENTIRE COLUMN REFERENCES.

Do you really have data in ALL rows in columns A and J? If not, you
could use the array formula

=3DSUM(IF(ISNUMBER(MONTH(A1:A1000)),(MONTH(A1:A1000)=AD=3D9)*(B1:B1000=3D"A=
")))

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?
> >

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?
>

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!

The fix is to correct the formulas that produce #N/A - in a
well-designed worksheet, you shouldn't get errors for expected results.

For instance, if your formula is a VLOOKUP like

=VLOOKUP(A1234, Sheet2!A:B, 2, FALSE)

then you could use

=IF(ISBLANK(A1234), "", VLOOKUP(A1234,Sheet2!A:B, 2, FALSE))



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

> I don't suppose there's a fix for that!

THANKS everyone. I did get my formula to work with one exception. The
column this fomula refers to (J) is also a formula based column and the
formula is filled quite a ways down the worksheet, meaning there are several
rows where #N/A shows until data is entered. If I make the ranges for
columns A and J only the populated rows, it works. If I use A:A and J:J, it
doesn't... I don't suppose there's a fix for that!

Thanks again - appreciated!

"JE McGimpsey" wrote:

> In article <[email protected]>,
> "Harlan Grove" <[email protected]> wrote:
>
> > You're wrong about entire rows. They work just fine. Might almost lead
> > one to believe Excel's programmers used unsigned short integers to
> > index arrays internally without realizing that it'd mean they couldn't
> > handle 65,536 entries in any dimension.
>
> Thanks for the correction, Harlan - I never use entire rows, and so I
> forget the reason. Perhaps one more time will make it stick.
>

Judy wrote...
>THANKS everyone. I did get my formula to work with one exception. The
>column this fomula refers to (J) is also a formula based column and the
>formula is filled quite a ways down the worksheet, meaning there are sever=
al
>rows where #N/A shows until data is entered. If I make the ranges for
>columns A and J only the populated rows, it works. If I use A:A and J:J, =
it
>doesn't... I don't suppose there's a fix for that!
..=2E.

You can't do what you want using entire column references like A:A and
J:J. If you keep using them, you'll keep getting errors. JE already
pointed this out to you. If error values as results are fine with you,
go on using entire column references. On the other hand, if you want
numeric results, YOU CAN'T USE ENTIRE COLUMN REFERENCES.

Do you really have data in ALL rows in columns A and J? If not, you
could use the array formula

=3DSUM(IF(ISNUMBER(MONTH(A1:A1000)),(MONTH(A1:A1000)=AD=3D9)*(B1:B1000=3D"A=
")))

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!

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

> You're wrong about entire rows. They work just fine. Might almost lead
> one to believe Excel's programmers used unsigned short integers to
> index arrays internally without realizing that it'd mean they couldn't
> handle 65,536 entries in any dimension.

Thanks for the correction, Harlan - I never use entire rows, and so I
forget the reason. Perhaps one more time will make it stick.

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

> Are you sure? Array fomulas for whole rows seem to work for me.

No, I was incorrect.

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?
> >

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!
>

JE McGimpsey wrote...
>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.

You're wrong about entire rows. They work just fine. Might almost lead
one to believe Excel's programmers used unsigned short integers to
index arrays internally without realizing that it'd mean they couldn't
handle 65,536 entries in any dimension.

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

Another possibility would be feeding invalid date serial numbers to
MONTH. Any nonnumeric values or numeric values outside 0 to
DATE(9999,12,31) will cause MONTH to return #VALUE!.

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?

"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> Your #NUM! error comes from the fact that you can't use entire columns
> (or rows) in array formulae

Are you sure? Array fomulas for whole rows seem to work for me.

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"

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?
>

Judy wrote...
>THANKS everyone. I did get my formula to work with one exception. The
>column this fomula refers to (J) is also a formula based column and the
>formula is filled quite a ways down the worksheet, meaning there are sever=
al
>rows where #N/A shows until data is entered. If I make the ranges for
>columns A and J only the populated rows, it works. If I use A:A and J:J, =
it
>doesn't... I don't suppose there's a fix for that!
..=2E.

You can't do what you want using entire column references like A:A and
J:J. If you keep using them, you'll keep getting errors. JE already
pointed this out to you. If error values as results are fine with you,
go on using entire column references. On the other hand, if you want
numeric results, YOU CAN'T USE ENTIRE COLUMN REFERENCES.

Do you really have data in ALL rows in columns A and J? If not, you
could use the array formula

=3DSUM(IF(ISNUMBER(MONTH(A1:A1000)),(MONTH(A1:A1000)=AD=3D9)*(B1:B1000=3D"A=
")))

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!

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?
> >

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?

The fix is to correct the formulas that produce #N/A - in a
well-designed worksheet, you shouldn't get errors for expected results.

For instance, if your formula is a VLOOKUP like

=VLOOKUP(A1234, Sheet2!A:B, 2, FALSE)

then you could use

=IF(ISBLANK(A1234), "", VLOOKUP(A1234,Sheet2!A:B, 2, FALSE))



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

> I don't suppose there's a fix for that!

"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> Your #NUM! error comes from the fact that you can't use entire columns
> (or rows) in array formulae

Are you sure? Array fomulas for whole rows seem to work for me.

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"

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!

JE McGimpsey wrote...
>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.

You're wrong about entire rows. They work just fine. Might almost lead
one to believe Excel's programmers used unsigned short integers to
index arrays internally without realizing that it'd mean they couldn't
handle 65,536 entries in any dimension.

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

Another possibility would be feeding invalid date serial numbers to
MONTH. Any nonnumeric values or numeric values outside 0 to
DATE(9999,12,31) will cause MONTH to return #VALUE!.

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 everyone. I did get my formula to work with one exception. The
column this fomula refers to (J) is also a formula based column and the
formula is filled quite a ways down the worksheet, meaning there are several
rows where #N/A shows until data is entered. If I make the ranges for
columns A and J only the populated rows, it works. If I use A:A and J:J, it
doesn't... I don't suppose there's a fix for that!

Thanks again - appreciated!

"JE McGimpsey" wrote:

> In article <[email protected]>,
> "Harlan Grove" <[email protected]> wrote:
>
> > You're wrong about entire rows. They work just fine. Might almost lead
> > one to believe Excel's programmers used unsigned short integers to
> > index arrays internally without realizing that it'd mean they couldn't
> > handle 65,536 entries in any dimension.
>
> Thanks for the correction, Harlan - I never use entire rows, and so I
> forget the reason. Perhaps one more time will make it stick.
>

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

> You're wrong about entire rows. They work just fine. Might almost lead
> one to believe Excel's programmers used unsigned short integers to
> index arrays internally without realizing that it'd mean they couldn't
> handle 65,536 entries in any dimension.

Thanks for the correction, Harlan - I never use entire rows, and so I
forget the reason. Perhaps one more time will make it stick.

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!
>

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

> Are you sure? Array fomulas for whole rows seem to work for me.

No, I was incorrect.