Functions/Formulas to count multiple variables
 

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.

Functions/Formulas to count multiple variables
 

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

Functions/Formulas to count multiple variables
 

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

"Jovan Timotijevic" wrote:



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

Functions/Formulas to count multiple variables
 

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.





"2many#s" wrote:

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

"Jovan Timotijevic" wrote:



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

Functions/Formulas to count multiple variables
 

=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


--
Regards,

Peo Sjoblom

"2many#s" wrote in message
...
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.





"2many#s" wrote:

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

"Jovan Timotijevic" wrote:



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

Functions/Formulas to count multiple variables
 

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" wrote:

=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


--
Regards,

Peo Sjoblom

"2many#s" wrote in message
...
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.





"2many#s" wrote:

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

"Jovan Timotijevic" wrote:



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

Functions/Formulas to count multiple variables
 

Thanks for the feedback


--
Regards,

Peo Sjoblom



"2many#s" wrote in message
...
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" wrote:

=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


--
Regards,

Peo Sjoblom

"2many#s" wrote in message
...
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.





"2many#s" wrote:

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

"Jovan Timotijevic" wrote:



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

Functions/Formulas to count multiple variables
 

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

"Peo Sjoblom" wrote:

Thanks for the feedback


--
Regards,

Peo Sjoblom



"2many#s" wrote in message
...
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" wrote:

=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


--
Regards,

Peo Sjoblom

"2many#s" wrote in message
...
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.





"2many#s" wrote:

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

"Jovan Timotijevic" wrote:



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