| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| multiple variables in sumproduct or if/then formulas | Excel Worksheet Functions | |||
| constructing (complex) variables with worksheet functions | Excel Discussion (Misc queries) | |||
| LOGICAL FUNCTIONS WITH MULTIPLE VARIABLES | Excel Worksheet Functions | |||
| Can I use variables for workheet name references in Excel functions? | Excel Discussion (Misc queries) | |||
| Can I use variables for workheet name references in Excel functions? | Excel Worksheet Functions | |||
Linear Mode
