Functions/Formulas to count multiple variables

[Guest]

Thanks in advance for any help you may have. Not sure if I need to use IF,
COUNTIF, ARRAY...etc.

I need to count a variable for one cell based on the variable in cell two.

Example - Need to count codes for multiple locations:
Location 1 codeA codeB codec
Location 2 code3 codeX code12

Need to know all of Location 1's codeA, codeB, etc.

This is probably an easy fix, but I'm stumped.

 

[Guest]

Lets say that you have a column A with the Location names, and next columns
are filled with the codes. Some locations have more codes than the other ones
and you want to count the codes. Is that right?

If you enter the name of the location to search for in B13 to count the
codes for this location, and location names are at A1:A10 try this:

=COUNTA(INDIRECT(ADDRESS(MATCH(B13,A1:A10,0),2,4)):INDIRECT(ADDRESS(MATCH(B13,A1:A10,0),10,4)))

Last number 10 means that you should enter no more than 10 codes for any
location

 

[Guest]

Yes you have the scenario correct. I'll try your idea.
Thanks!

 

[Guest]

Nope, didn't work.

Can we try another idea?

Headers: Location codeA codeB codeC

Row 1: LOC1 M 5 12
Row 2: LOC2 M 3 X
Row 3: LOC1 F 9 4
etc.

Need to count all "M"s for LOC1, then LOC2, etc.
Need to count all "F"s for LOC1, then LOC2, etc.

8 Separate locations, 10 to 15 different codes

I'm assuming there needs to be some filtering involved.

Thanks again, I appreciate any help.

 

[Peo Sjoblom]

=SUMPRODUCT(--(A2:A50="LOC1"),--(B2:B50="M"))

will give the count of LOC1 and M, replace the hardcoded values with for
instance

=SUMPRODUCT(--(A2:A50=H2),--(B2:B50=I2))

where H2 is the location and I2 codeA values

change the A2:A50 and B2:B50 to real life ranges

 

[Guest]

Dear Peo,

You are my HERO!!!! Thank you - its working!!!! You have solved a huge
problem for me.

Thanks to everyone who came up with ideas.

KP

 

[Peo Sjoblom]

Thanks for the feedback

 

[Guest]

Dear Peo,

One more question: I tried to enter a range of J3:Q50 as the 2nd ARRAY, and
got the VALUE! error code. I need to be able to select from a range like
this.
Any suggestions?


=SUMPRODUCT(--(A2:A50="LOC1"),--(J3:Q50="M"))

Thanks again,
Kelly