Forum Discussion
calculating an age range across multiple sheets
I am trying to extrapolate an age range (i.e. 20-29) using information contained with in the same columns but on different sheets. I tried to find the value of age 20 on a single sheet using the formula =COUNTIF(ACTIVE!N:N, "20"), but the value is clearly incorrect. By looking at the column on just the one sheet, there should have been "0" age 20, but it gave me "28" . There are 27 entries in that column of ages ranging from 24-92.
suggestions?
5 Replies
MarkS1850 Can you perhaps show us a sample of your data? Try this: CLick on the column N header to select the entire column. Press control+F on your keyboard. Make sure the Options section of the Find dialog is expanded. Set it to "Look in" Values. Let it search for '20' (without those quotes), check the box "Match entire cell contents". Then click Find Next. It should find something, as the COUNTIF function is usually correct.
JKPieterse Thank you for responding. The column I am searching has values that were discovered using a formula to calculate age based on birthday vs current day: =DATEDIF(M38, TODAY(),"Y"). I tried you suggestion and I am not sure what was supposed to happen. The cell I wanted to collect the total number of cells with the age "80" (I used that number because I knew there were more than one appearance of that age with in the column). The cell that I typed the formula in did not record the number of times that age appeared. Actually, it did not record anything.
- What happens if you remove the quotes around the age you are looking for from the formula? It now counts cells with the text "20" in them, which is different from cells with the number 20.
