Excel Array Formula Query

  The Regster 09:45 14 Apr 2008
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,"")))}


Many thanks in advance.

The Regster

  bnorth 09:58 14 Apr 2008

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

sorry if this isn't helpful

  The Regster 10:04 14 Apr 2008

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 2008

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 2008

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 2008

If you have XL 2007

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

  The Regster 10:32 14 Apr 2008

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 2008

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 2008

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.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

Add Depth Of Field to a photo using Tilt Shift Blur in Photoshop

iPhone tips & tricks

Les meilleures tablettes 2017