Aggregating Counts and Array Functions -- HELP!

dmacdougall

New Member
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?!

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
=sumproduct((A\$1:A\$20="FL")*(B\$1:B\$20="YES))

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

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?

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

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

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

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.

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?

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

Replies
5
Views
164
Replies
1
Views
80
Replies
14
Views
217
Replies
10
Views
366
Replies
5
Views
630

1,203,462
Messages
6,055,562
Members
444,799
Latest member
CraigCrowhurst

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back