Nested IFs and Arrays

[smilinggm]

I have a table with two columns, X and Y.
I also have a list of desirable values of X and a list of desirable values of Y.
I am interested in the count of entries in the table that have both a valid entry in column X and a valid entry in column Y and I need a single formula that only refers to whole columns and whole lists.

How can I write an array formula that will check down column X for entries that exist in the validation list for X and then count all of those that also have an entry in column Y that exists in the validation list for Y?

If there were only one column and list I could use:
SUM(COUNTIF(column X, validation list X))
as an array formula.

I have tried using:
SUM(IF(column X = validation list X, IF(column Y = validation list Y, 1, 0) 0))
but this does not work.

 

[seboseszuk]

Hi,

I have a workbook with your problem solved.
a2:a18 X
B12:b18 Y
D2:d4 valid X
E2:e3 valid Y

Enter formula in G2
pull down to G18
A number 1 indicates an incident of both X and Y both being valid.
The number of 1.s is your answer.
I can't copy my formula here. If interested drop a line at
gabelene@townisp.com

HTH
Gabor

 

[smilinggm]

Hi,

I have a workbook with your problem solved.
a2:a18 X
B12:b18 Y
D2:d4 valid X
E2:e3 valid Y

Enter formula in G2
pull down to G18
A number 1 indicates an incident of both X and Y both being valid.
The number of 1.s is your answer.
I can't copy my formula here. If interested drop a line at
gabelene@townisp.com

HTH
Gabor

Thanks for this, but I need to do this without adding an extra column. It might sound like I'm just making things harder for myself, but the spreadsheet I'm working on has a lot of stuff going on and so my specifications are very specific.

So I need one formula in one cell that will return the count.

 

[Aladin Akyurek]

I have a table with two columns, X and Y.
I also have a list of desirable values of X and a list of desirable values of Y.
I am interested in the count of entries in the table that have both a valid entry in column X and a valid entry in column Y and I need a single formula that only refers to whole columns and whole lists.

How can I write an array formula that will check down column X for entries that exist in the validation list for X and then count all of those that also have an entry in column Y that exists in the validation list for Y?

If there were only one column and list I could use:
SUM(COUNTIF(column X, validation list X))
as an array formula.

I have tried using:
SUM(IF(column X = validation list X, IF(column Y = validation list Y, 1, 0) 0))
but this does not work.

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(MATCH(X2:X100,Xlist,0)),IF(ISNUMBER(MATCH(Y2:Y100,Ylist,0)),1)))

Or just enter:

=SUMPRODUCT(--ISNUMBER(MATCH(X2:X100,Xlist,0)),--ISNUMBER(MATCH(Y2:Y100,Ylist,0)))