Aggregating Counts and Array Functions -- HELP!

[dmacdougall]

OK, I've been puzzling about this one for a few days, and I can't find any old posts that are on point, but I'm sure that they are out there somewhere.

I also know this is a simple question, but always seem to miss the obvious.

I have two columns of data, Column A and B. I'd like to count the number of rows in which the value in Column A equals one value, and the value in Column B equals another value.

But, I'd like to do this all on one formula, for multiple rows, so as to obtain an aggregrate total, telling me the total number of matches found for each row.

I can't use a array formula (Mr. Excel terms these "CSE Formulas") as the array matches all instances against each other, and I need to query row by row. Here is one formula I came up with, but I need help to be able to use this formula over a range of rows at one time, other than just repeating the same formula over and over again, substituting the next row each time.

How can I do this with this formula, other than in an array?

=Count(And(If(A2="FL",1,0),If(B2="Yes",1,0)))

An array might look like this, but as I said above, simply creating an array does not work, as I don't know how to tell Excel to not count every possible match, but only matches that occur in the same row...

{=Count(And(If(A2:A10="FL",1,0),If(B2:B10="Yes",1,0)))}

Help! Why can I figure out the obvious solution to this problem?!

 

[Legacy 51064]

=sumproduct((A$1:A$20="FL")*(B$1:B$20="YES))

 

[PaddyD]

hi - welcome to the board1

could you post a small, representative sample of data and give an indication of the answers you expect, given the data...

 

[dmacdougall]

Reply for PaddyD

Thanks for the welcome! (First post, though I've been reading for a while...)

Here you go:


Column A Column B

FL Yes
FL Yes
FL No
GA Yes
GA No
LA No
MD Yes

So, I'm hoping that the SumProduct idea will work -- I'm going to plug it in now. Basically, if I've got thirty Florida's with a Yes, and ten with a No, I just want to know that I have thirty Yes's and ten No's, preferablly using one formula to check each row. Does this make sense?

 

[PaddyD]

a pivot table would probably be easier, assuming you want the counts for all classings...

 

[dmacdougall]

Reply for 98illni

Thank you, but one question -- what exactly does the "*" do?

Is this Excel's version of the boolean "and"? Please advise... thanks! dm

 

[PaddyD]

http://www.mrexcel.com/board2/viewtopic.php?t=59063

 

[Legacy 51064]

That is my guess, but Alladin might think it is something else.

If you want, you could replace the "FL" and "Yes" with cell references so that as you change the states or responses, then you would not have to change the formula.

 

[dmacdougall]

Second Reply for PaddyD

Thank you again for your help, and the reference to Aladin's article. However, I've already read this article, and it would seem from it that the "*" simply multiplies one array against the other, pairing the values by their places in the array. Is this a correct assessment of this situation?

 

[dmacdougall]

Second Reply for 98illni

Thank you for your suggestion, but as I'm working with text values, I'm afraid the SumProduct function does not seem to work. Perhaps it would if I converted everything into logical True and False (1 and 0) statements? Please advise, if able...