Array formula?

[davegb]

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!

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Dave Miller]

I would use a database function:

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

Using this structure:

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

 

[Dana DeLouis]

Hi. Excel 20007:

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

 

[davegb]

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)






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

 

[davegb]

=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]

I would use a database function:

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

Using this structure:

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

 

[davegb]

Hi. Excel 20007:

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

--
HTH :>)
Dana DeLouis






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

 

[Dana DeLouis]

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.

 

[Bob Phillips]

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?