| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching alfanumaric cell contents and returning with a numaric v
Is there a way to use a formula to determine how many times an alphanumeric
value is used in a column of cells. For example a column consisting of 300 cells that have one of the folloing notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3". Can a formula be used to determine how many times A2 appears in the column if so what formula would I use? Can a formula be used to determine how many times A1 or B3 or C2 appears in the column if so what formula would I use? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Searching alfanumaric cell contents and returning with a numaric v
Try this:
=COUNTIF(A1:A300,"A1") -- Biff Microsoft Excel MVP "ksean" wrote in message ... Is there a way to use a formula to determine how many times an alphanumeric value is used in a column of cells. For example a column consisting of 300 cells that have one of the folloing notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3". Can a formula be used to determine how many times A2 appears in the column if so what formula would I use? Can a formula be used to determine how many times A1 or B3 or C2 appears in the column if so what formula would I use? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Searching alfanumaric cell contents and returning with a numar
Thanks it worked great however now I need to expand the search across 2
columns on a different sheet. For example: I need cell B5 on sheet 'stock' to tell me how many times column D on sheet 'Master' displays "blue" and column E displays "frisbee". I am trying to determine how many times "blue" and "frisbee" appear on the same row side by side. Thanks, Kerry "T. Valko" wrote: Try this: =COUNTIF(A1:A300,"A1") -- Biff Microsoft Excel MVP "ksean" wrote in message ... Is there a way to use a formula to determine how many times an alphanumeric value is used in a column of cells. For example a column consisting of 300 cells that have one of the folloing notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3". Can a formula be used to determine how many times A2 appears in the column if so what formula would I use? Can a formula be used to determine how many times A1 or B3 or C2 appears in the column if so what formula would I use? . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Searching alfanumaric cell contents and returning with a numar
Try this...
=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee")) Better to use cells to hold the criteria: A1 = blue B1 = frisbee =SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1)) -- Biff Microsoft Excel MVP "ksean" wrote in message ... Thanks it worked great however now I need to expand the search across 2 columns on a different sheet. For example: I need cell B5 on sheet 'stock' to tell me how many times column D on sheet 'Master' displays "blue" and column E displays "frisbee". I am trying to determine how many times "blue" and "frisbee" appear on the same row side by side. Thanks, Kerry "T. Valko" wrote: Try this: =COUNTIF(A1:A300,"A1") -- Biff Microsoft Excel MVP "ksean" wrote in message ... Is there a way to use a formula to determine how many times an alphanumeric value is used in a column of cells. For example a column consisting of 300 cells that have one of the folloing notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3". Can a formula be used to determine how many times A2 appears in the column if so what formula would I use? Can a formula be used to determine how many times A1 or B3 or C2 appears in the column if so what formula would I use? . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Searching alfanumaric cell contents and returning with a numar
I can't get your formula to work as you have shown it, in your formula what
does the -- stand for? I am still missing something! "T. Valko" wrote: Try this... =SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee")) Better to use cells to hold the criteria: A1 = blue B1 = frisbee =SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1)) -- Biff Microsoft Excel MVP "ksean" wrote in message ... Thanks it worked great however now I need to expand the search across 2 columns on a different sheet. For example: I need cell B5 on sheet 'stock' to tell me how many times column D on sheet 'Master' displays "blue" and column E displays "frisbee". I am trying to determine how many times "blue" and "frisbee" appear on the same row side by side. Thanks, Kerry "T. Valko" wrote: Try this: =COUNTIF(A1:A300,"A1") -- Biff Microsoft Excel MVP "ksean" wrote in message ... Is there a way to use a formula to determine how many times an alphanumeric value is used in a column of cells. For example a column consisting of 300 cells that have one of the folloing notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3". Can a formula be used to determine how many times A2 appears in the column if so what formula would I use? Can a formula be used to determine how many times A1 or B3 or C2 appears in the column if so what formula would I use? . . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Searching alfanumaric cell contents and returning with a numar
I can't get your formula to work
Kind of vague. What exactly does that mean? You get an error? You get the wrong result? You don't get any result? Your hard drive crashed? Post the *exact* formula you tried. what does the -- stand for? See this: http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "ksean" wrote in message ... I can't get your formula to work as you have shown it, in your formula what does the -- stand for? I am still missing something! "T. Valko" wrote: Try this... =SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee")) Better to use cells to hold the criteria: A1 = blue B1 = frisbee =SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1)) -- Biff Microsoft Excel MVP "ksean" wrote in message ... Thanks it worked great however now I need to expand the search across 2 columns on a different sheet. For example: I need cell B5 on sheet 'stock' to tell me how many times column D on sheet 'Master' displays "blue" and column E displays "frisbee". I am trying to determine how many times "blue" and "frisbee" appear on the same row side by side. Thanks, Kerry "T. Valko" wrote: Try this: =COUNTIF(A1:A300,"A1") -- Biff Microsoft Excel MVP "ksean" wrote in message ... Is there a way to use a formula to determine how many times an alphanumeric value is used in a column of cells. For example a column consisting of 300 cells that have one of the folloing notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3". Can a formula be used to determine how many times A2 appears in the column if so what formula would I use? Can a formula be used to determine how many times A1 or B3 or C2 appears in the column if so what formula would I use? . . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Searching alfanumaric cell contents and returning with a numaric v
=COUNTIF(A1:A300,"A1")
etc. -- Gary''s Student - gsnu200907 "ksean" wrote: Is there a way to use a formula to determine how many times an alphanumeric value is used in a column of cells. For example a column consisting of 300 cells that have one of the folloing notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3". Can a formula be used to determine how many times A2 appears in the column if so what formula would I use? Can a formula be used to determine how many times A1 or B3 or C2 appears in the column if so what formula would I use? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Searching alfanumaric cell contents and returning with a numar
Thanks it worked great however now I need to expand the search across 2 columns on a different sheet. For example: I need cell B5 on sheet 'stock' to tell me how many times column D on sheet 'Master' displays "blue" and column E displays "frisbee". I am trying to determine how many times "blue" and "frisbee" appear on the same row side by side. Thanks, Kerry "Gary''s Student" wrote: =COUNTIF(A1:A300,"A1") etc. -- Gary''s Student - gsnu200907 "ksean" wrote: Is there a way to use a formula to determine how many times an alphanumeric value is used in a column of cells. For example a column consisting of 300 cells that have one of the folloing notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3". Can a formula be used to determine how many times A2 appears in the column if so what formula would I use? Can a formula be used to determine how many times A1 or B3 or C2 appears in the column if so what formula would I use? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| finding the cell with min value and returning the contents of neig | Excel Worksheet Functions | |||
| Returning contents of a cell in another sheet in same workbook | Excel Discussion (Misc queries) | |||
| Searching in another workbook for a string and returning with the contents of the cell next to it. | Excel Worksheet Functions | |||
| Returning contents of a cell | Excel Worksheet Functions | |||
| LOOKUP returning value in cell above what I was searching for | Excel Discussion (Misc queries) | |||
Hybrid Mode
