IF STATEMEMNTS

J

Judy

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

David Billigmeier

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,
 
J

JE McGimpsey

One way:

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

Or if months:

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

Judy

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

JE McGimpsey

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

Judy

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

JE McGimpsey

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

Sandy Mann

JE McGimpsey said:
Your #NUM! error comes from the fact that you can't use entire columns
(or rows) in array formulae
Click to expand...

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

--
Regards

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


"
 
H

Harlan Grove

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.
Click to expand...

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.
Click to expand...

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

JE McGimpsey

Harlan Grove said:
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.
Click to expand...

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

Judy

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

JE McGimpsey

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))
 
H

Harlan Grove

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 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!
Click to expand...
....

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

=SUM(IF(ISNUMBER(MONTH(A1:A1000)),(MONTH(A1:A1000)­=9)*(B1:B1000="A")))
 
Top