IF and COUNTIF Combo
I am running a stats report for work. Previously, the one running the stats simply went through and counted the 100 sheets of a certain cell. They can say 1 of 5 things, all are words. I want to create a formula that will count if a certain cell (that is uniformly G5 on all sheets) says the one of 5 words, and place the total times it says that in one of five empty cells with the formula on the first sheet. For example, if G5 of sheets FIRST:LAST! say "Apple" ten times in cell G5 then I want it to say 5. So if in g5 apple is listed 10 times, orange 5 and pear 3, then it should say
Apple 10
Orange 5
Pear 3
Berry 7
Pineapple 8
Any suggestions? Been at it for over an hour and keep getting errors!
There's no inbuilt 3D COUNTIF function, i.e. a function that can count a specific condition on multiple sheets......but there is a workaround.......
.....make a list of all the sheet names on the summary sheet, if that list is in A2:A100 then use this formula
=SUMPRODUCT(COUNTIF(INDIRECT("'"&A$2:A$100&"'!G5"),C2))
where C2 contains Apple
Note: you may be able to get rid of the list if you have a consistent pattern for the sheet names, like sheet1 to sheet100 for instance.....
Audere est facere
Hi CollieGarv and welcome to the forum,
I have written some code that will look through all sheets at cell G5 and count the occurences of what is there.
See the attached. Some special things. This always puts answers on Sheet1 starting below the last cell in column A that has stuff in it.
Hope this helps.
One test is worth a thousand opinions.
Click the * below to say thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks