Multi-conditional Array Count

[gilmanpt]

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
Define: Headcount as B1:B6

Cell C1 contains:
Count(IF(Location="NY",Headcount,0))

I entered by "shift+control+enter"

This is what shows up in C1:
{Count(IF(Location="NY",Headcount,0))}

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

Thanks for your help!

 

[Peter_SSs]

gilmanpt

Welcome to the Mr Excel board!

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

Edit: Or, using your named ranges:
=SUMIF(Location,"NY",Headcount)

 

[just_jon]

Another option would be a Pivot Table.

 

[gilmanpt]

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!

 

[Peter_SSs]

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.

 

[gilmanpt]

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
Define: Headcount as B1:B6, D1:D3

{Count(IF(Location="NY",Headcount,0))} Returns 0
SUMIF(Location,"NY",Headcount) Returns !Value error

It returns a non-zero value if I

Define: Location as A1:A6
Define: Headcount as B1:B6

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

Thanks again for your advice...

 

[just_jon]

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.

 

[gilmanpt]

thanks, this should work! You guys are great!

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