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

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

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 ,
"Judy" 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 ,
"Judy" 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 ,
"Judy" 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 ,
"Judy" 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" wrote in message
...
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

Replace@mailinator with @tiscali.co.uk


"

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 .com,
"Harlan Grove" 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 ,
"Sandy Mann" 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 .com,
"Harlan Grove" 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.

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 ,
"Judy" 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:A100 0)=AD=3D9)*(B1:B1000=3D"A=
")))