Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting values in mulitple worksheets
I am trying to count values in a specific cell on multiple worksheets. (The
cell is in the same location on all the worksheets). Something like this: Count all values = to "Y" in cell A24 on all worksheets. Anyone have any suggestions? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting values in mulitple worksheets
It can be somewhat complicated if you have a lot of sheets.
List your sheet names in a range of cells. Assume this list is in the range G1:G10. =SUMPRODUCT(COUNTIF(INDIRECT("'"&G1:G10&"'!A24")," Y")) It might be easier to just use another cell (the same cell) on each sheet with this formula: =--(A24="Y") Then just use a sum formula like this: =SUM(Sheet1:Sheet10!A1) -- Biff Microsoft Excel MVP "Joan" wrote in message ... I am trying to count values in a specific cell on multiple worksheets. (The cell is in the same location on all the worksheets). Something like this: Count all values = to "Y" in cell A24 on all worksheets. Anyone have any suggestions? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting values in mulitple worksheets
This code works (sort of). It creates a user defined function. the
only bug is that it won't work if the "ref" variable is the same as the cell the formula is currently in. To use the formula, enter countif_multiple_tabs("Y", "A24"). Per my first statement, this will work so long as you don't put the formula in cell A24. Function countif_multiple_tabs(search_string As String, ref As String) search_count = 0 For i = 1 To Application.Worksheets.Count If Worksheets(i).Range(ref).Value = search_string Then search_count = search_count + 1 Next countif_multiple_tabs = search_count End Function On Jul 3, 8:36*pm, Joan wrote: I am trying to count values in a specific cell on multiple worksheets. (The cell is in the same location on all the worksheets). *Something like this: * Count all values = to "Y" *in cell A24 on all worksheets. * Anyone have any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging data from mulitple worksheets | Excel Worksheet Functions | |||
Counting occurance of text values across multiple worksheets | Excel Worksheet Functions | |||
How do I sum totals from mulitple worksheets | Excel Worksheet Functions | |||
mulitple worksheets vlookup | Excel Discussion (Misc queries) | |||
Changes to Mulitple Worksheets | Excel Worksheet Functions |