Array formula?
 

I want a count based on 2 criteria in columns E & F in rows 3 to 29 in
the sheet. For example, I want to sum the number of times where both
an "A" occurs in column E and a "y" occurs in column F. I understand
that this would require an array formula, but haven't worked with them
much and don't really understand them at all. Can someone suggest the
formula for doing this?
Thanks!

Array formula?
 

It doesn't.

=SUMPRODUCT(--(E3:E29="A"),--(F3:F29="y"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"davegb" wrote in message
ups.com...
I want a count based on 2 criteria in columns E & F in rows 3 to 29 in
the sheet. For example, I want to sum the number of times where both
an "A" occurs in column E and a "y" occurs in column F. I understand
that this would require an array formula, but haven't worked with them
much and don't really understand them at all. Can someone suggest the
formula for doing this?
Thanks!

Array formula?
 

=COUNTIF(E3:E29,"A")+COUNTIF(F3:F29,"Y")

"davegb" wrote:

I want a count based on 2 criteria in columns E & F in rows 3 to 29 in
the sheet. For example, I want to sum the number of times where both
an "A" occurs in column E and a "y" occurs in column F. I understand
that this would require an array formula, but haven't worked with them
much and don't really understand them at all. Can someone suggest the
formula for doing this?
Thanks!

Array formula?
 

I would use a database function:

=DCOUNTA(E3:F29,1,I1:J2)

Using this structu

E F I J
1 Col1 Col2
2 A Y
3 Col1 Col2
4 A Y
5 B A
6 C A
7 A Y
8 E A


Regards,
David Miller

Array formula?
 

Hi. Excel 20007:

=COUNTIFS(E3:E29,"A",F3:F29,"y")

--
HTH :)
Dana DeLouis


"davegb" wrote in message
ups.com...
I want a count based on 2 criteria in columns E & F in rows 3 to 29 in
the sheet. For example, I want to sum the number of times where both
an "A" occurs in column E and a "y" occurs in column F. I understand
that this would require an array formula, but haven't worked with them
much and don't really understand them at all. Can someone suggest the
formula for doing this?
Thanks!

Array formula?
 

On Apr 27, 9:50 am, "Bob Phillips" wrote:
It doesn't.

=SUMPRODUCT(--(E3:E29="A"),--(F3:F29="y"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"davegb" wrote in message

ups.com...



I want a count based on 2 criteria in columns E & F in rows 3 to 29 in
the sheet. For example, I want to sum the number of times where both
an "A" occurs in column E and a "y" occurs in column F. I understand
that this would require an array formula, but haven't worked with them
much and don't really understand them at all. Can someone suggest the
formula for doing this?
Thanks!- Hide quoted text -

- Show quoted text -

Very interesting! Never saw the -- in XL before. It does create a
problem though in that it automatically merges 2 cells when I paste it
in. Why is this and is there a way to put this formula in a single
cell?

Array formula?
 

On Apr 27, 9:56 am, Mike wrote:
=COUNTIF(E3:E29,"A")+COUNTIF(F3:F29,"Y")


Thanks, but this would give me the total of the A's in column E and
the y's in column F. I want the number of occurences where there is
both a A in E and a y in F.



"davegb" wrote:
I want a count based on 2 criteria in columns E & F in rows 3 to 29 in
the sheet. For example, I want to sum the number of times where both
an "A" occurs in column E and a "y" occurs in column F. I understand
that this would require an array formula, but haven't worked with them
much and don't really understand them at all. Can someone suggest the
formula for doing this?
Thanks!- Hide quoted text -

- Show quoted text -

Array formula?
 

On Apr 27, 10:15 am, Dave Miller wrote:
I would use a database function:

=DCOUNTA(E3:F29,1,I1:J2)

Using this structu

E F I J
1 Col1 Col2
2 A Y
3 Col1 Col2
4 A Y
5 B A
6 C A
7 A Y
8 E A

Regards,
David Miller

Very interesting! Will have to do some research on this one. It
definitely doesn't work to just cut and paste it. When I have some
time....

Array formula?
 

On Apr 27, 10:51 am, "Dana DeLouis" wrote:
Hi. Excel 20007:

=COUNTIFS(E3:E29,"A",F3:F29,"y")

--
HTH :)
Dana DeLouis

"davegb" wrote in message

ups.com...



I want a count based on 2 criteria in columns E & F in rows 3 to 29 in
the sheet. For example, I want to sum the number of times where both
an "A" occurs in column E and a "y" occurs in column F. I understand
that this would require an array formula, but haven't worked with them
much and don't really understand them at all. Can someone suggest the
formula for doing this?
Thanks!- Hide quoted text -

- Show quoted text -

Thanks for the idea. Maybe it's because I'm using XL2000, but I have
no COUNTIFS function, and get a #NAME error on your formula, whether I
enter it nromally or as an array forumula.

Array formula?
 

Hi. Yes, it's an Excel 2007 function.
You didn't mention which version you had, so I thought I'd throw it out as
an alternative.
--
Dana DeLouis

Thanks for the idea. Maybe it's because I'm using XL2000, but I have
no COUNTIFS function, and get a #NAME error on your formula, whether I
enter it nromally or as an array forumula.

=COUNTIFS(E3:E29,"A",F3:F29,"y")

Array formula?
 

That shouldn't happen, try pasting it into the formula bar, rather than
direct cell entry.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Very interesting! Never saw the -- in XL before. It does create a
problem though in that it automatically merges 2 cells when I paste it
in. Why is this and is there a way to put this formula in a single
cell?