# Multi-conditional Array Count

#### gilmanpt

##### New Member
Hi,

I have
A1 to A6: NY, TX, CA, TX, NY, CA
B1 to B6: 1, 0, 0, 1, 1,1

I would like to track the statistical showing for each state. ie end results should be:
NY 2
TX 1
CA 1

This is what I Have done so fare.
Define: Location as A1:A6

Cell C1 contains:

I entered by "shift+control+enter"

This is what shows up in C1:

I keep on getting 0 for the the result of C1?? The answer should be 2.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
gilmanpt

Welcome to the Mr Excel board!

In cell C1, try:
=SUMIF(A1:A6,"NY",B1:B6)

Edit: Or, using your named ranges:

Another option would be a Pivot Table.

Peter, thanks!

I think I found my problem - I don't really have an array in my spreadsheet (I simplified the problem for this board). If I have a simple array, both Sumif and Count work.

I might need to repost to ask the question in a different way.

Here's it is:

------------
if I have A1:A4 and C1:C5 in the first array, and then B1:b4 and D1:D5 in the second array.

Can I still do the array functions?

I hope this is clear... thanks in advance!

gilmanpt

Can you tell us what is in the arrays and what you are trying to do?

Perhaps post a small sample of your worksheet? To do so, see the link at the bottom of this page to Colo's HTML Maker. Also read the ‘Sticky’ titled: 'HTML Maker FAQ (How to show your sheet on the board)' which is at the top of this ‘Excel Questions’ forum.

Test it out in the ‘Test Here’ forum which is found in the ‘MrExcel Message Board Forum Index’ before trying to use it in this forum.

Hi Peter,

Sorry about that... Hopefully this is more clear:

A1-A6 = NY, CA, TX, NY, CA, TX
C1-C3 = CA, NY, TX

B1-B6= 1, 0, 1, 1, 1, 0
D1-D3 = 1, 0, 0

Define: Location as A1:A6, C1:C3

It returns a non-zero value if I

Define: Location as A1:A6

I have huntch that Arrays cannot be broen up into different columns...

A couple ways:

=COUNTIF(A1:A6,X1)+COUNTIF(C1:C3,X1)

Where X1 is a target state. Or

=SUM(IF(A1:C6=X1,1,0))

Which needs to be entered with Control + Shift + Enter and is probably overkill

Both of the above assume that a COUNT of the state would be == to a SUM of the state values.

thanks, this should work! You guys are great!

Just to confirm though - arrays cannot handle a group of non-adjacent cells right?

Replies
5
Views
127
Replies
5
Views
212
Replies
3
Views
399
Replies
0
Views
174
Replies
16
Views
273

1,196,513
Messages
6,015,630
Members
441,912
Latest member
Rayna_rahman00

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

### Which adblocker are you using?

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