# Excel Array Formula Query

The Regster 09:45 14 Apr 08
Locked

Good morning all,

Hope someone can help with the following array in Excel - I'm up against it at work this morning and this is driving me mad...

I want to count the no. of Y's and N's in a series of columns.

Col D contains the master Y/N, followed by E that contains Y/N answer to question.

I want to count the no of Y/N in Col E based on the response in Col D i.e. count Y's in E where there is also a 'Y' in D.

I have tried using the following array and it is not working (returns zero, but there are at some D=Y and E=Y).

Could anyone out there have a look and tell what I'm doing wrong. (BTW - I have remembered to use Ctrl Shft & Enter!)...

[=COUNT(IF(\$D\$4:\$D\$38="Y",IF(\$E\$4:\$E\$38="Y",E\$4:E\$38,"")))}

The Regster

bnorth 09:58 14 Apr 08

I'm not red hot on EXCEL but after a play I think
that, say,
IF(\$D\$2:\$D\$8="Y",1,0)
returns a 1 if there are a majority of Y's in that range.
I'm not sure how you can do the conditional count you want but I'll have a go.

are you sure you don't want to just use a pivot table of counts of col D vs E ? its under data ..

The Regster 10:04 14 Apr 08

Many thanks for the response bnorth.

Not hot on pivot tables myself, so not too sure if they can help.

However, what I want to do is drag the formula in Col E across to Col X. Not sure that Pivot will do this. Also, the results are being turned into a % Y / N to feed into a bar graph and I'm not sure that Pivot will help there either.

I have done simliar with sum(if arrays no problem, but for some reason count(if is not responding the same way.

Thanks again.

bnorth 10:05 14 Apr 08

or to count the number of Y (Ns) in E conditional on a Y in the D col there is also sumproduct used like this

=SUMPRODUCT(--(D2:D8="Y"),--(E2:E8="Y"))
=SUMPRODUCT(--(D2:D8="Y"),--(E2:E8="N"))

bnorth 10:13 14 Apr 08

you've lost me a bit I'm afraid
I thought you just wanted 2 counts - a count of Y's in E (where we have a Y in D) and a count of N's in R (where we have a Y in D).

the pivot table will do that (it'll do a cross tab of D and Y and you can get %'s I think or if not se a simle formula) and sumproduct will also do that.

VoG II 10:25 14 Apr 08

If you have XL 2007

=COUNTIFS(\$D4:\$D38,"Y",E4:E38,"Y")

The Regster 10:32 14 Apr 08

Am using Excel 02 and it doesn't recognise countifs. I think XL 07 is worth the £300 for that formula alone. Thanks though.

bnorth
Thanks for sum product - it is not giving me quite what I want - it is giving me 1 more result than I can count manually (brings back 4 when I know there are 3).

VoG II 12:10 14 Apr 08

The SUMPRODUCT formula

=SUMPRODUCT(--(\$D4:\$D38="Y"),--(E4:E38="Y"))

gives me the same result as the COUNTIFS formula and tallies with the matches that I count manually.

The Regster 12:14 14 Apr 08

Apologies both - my manual count was incorrect so sum product is fine.

Very many thanks for that - it has saved me a huge amount of work.

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation technologies coming to Siggraph 2017

iPad Pro 12.9 vs Surface Pro 5