Counting the amount of times "available" is stated within specicifc cells on sheets

[ahalliwell]

Hi all,

Just a quick one, I think I have a brain block today and this might be very easy to do and I'm just being daft.

Basically I want to do the following

Count the amount of times "available" is stated within cell B3 on 15 different sheets (each sheet is called "sheet1" "sheet2"... and so on

What formulae do I use to tell me how many times that word is displayed?

I have tried

Please Login or Register  to view this content.

But that only counts as 1 when they all display "available"

Like I said this may be very easy, and I assume it is but for some reason my brain has stopped working.

[Jonmo1]

I would suggest a helper cell on each sheet, in say C3 (or wherever you want).
In C3 of each sheet put, =(B3="Available")+0

Then use
=SUM('Sheet1:Sheet15'!C3)

[Glenn Kennedy]

Use:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&E2:E16&"'!B3"),"available"))

with a sheet list in E2 to E16.

[Richard Buttrey]

Hi,

Just as a matter of interest what are the sheets for?

I ask because this crops up a lot. If the sheets are capturing the same information but for different values of the same TYPE of thing, i.e. if the sheets represent say months (Jan, Feb.. etc), or departments, or product codes, or staff names, or cities, or....but I guess you get the idea, then to aid reporting you should put all the data on the same sheet but with an extra column in which you record what was previously the sheet name. For instance if the sheets are all months, then the additional column will be headed Months and will contain the month name for each record.

This simplifies any analysis and makes producing summary reports so much easier, and it also opens your data to Pivot Table analysis which is far more efficient and avoids the need to use either formulae or VBA.

[ahalliwell]

Thank you for the speedy response, the sheets are individual names, the reason I made it 1sheet per person was there is manual input data on each sheet that the individual has to enter at the start of each week, the separate sheets are to make it easier for the individuals to find their sheet.

Here's a daft question with the above subproject answer how do i reference the sheets instead of the cells.

[Glenn Kennedy]

You can generate the list of sheetnames automatically, for example on a hidden sheet. I can't show you now, but I'll post a solution in about 3 hours.

[Richard Buttrey]

Thank you for the speedy response, the sheets are individual names, the reason I made it 1sheet per person was there is manual input data on each sheet that the individual has to enter at the start of each week, the separate sheets are to make it easier for the individuals to find their sheet.

That doesn't prevent you having a single common data entry form, whose details on completion would be added to the database.

And since all the data would be in a normalised 2 dimensional database then a simple Advanced Filter macro could extract any person's details to a single template sheet. i.e. the individual still sees all their data on a sheet and you avoid the clutter of having sheets for everyone.

You really would simplify your process were you to adopt this approach. And if you ever need to build additional functionality and reporting you are seriously compromising your ability to do so if you don't.

Believe me, been there and got dozens of T shirts.

[ahalliwell]

Hmmmm

You make a valid point Richard but it appears you have more faith in my excel abilities than I do lol

I would love to be able to build in those sorts of things as like you said it would make life so much easier.

Thanks for the hints though, maybe I can given it and go, can't help to try? Lol

[Glenn Kennedy]

There's a lot going on here. Two named ranges (CTRL-F3 to view) and old inbuilt Excel macro, almost forgotten, but still there; an array formula, in A2, of the sheet "Lists" - which can be hidden:

=IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),SMALL(IF(MID(Sheets,FIND("]",Sheets)+1,255)<>MID(CELL("filename",Master!A2),FIND("]",CELL("filename",Master!A2))+1,255),ROW(INDIRECT("$A$1:$A$"&COUNTA(MID(Sheets,FIND("]",Sheets)+1,255))))),ROW($A1:$A1)),1),"")

to generate the list of sheetnames (a named range "sheetlist" currently set to a maximum of 99),
and an ordinary formula in Master to return the result:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheetlist&"'! B3"),"available"))


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

happy to explain further, but try to see if it's what you want, first.

Remember: ENALBLE MACROS on opening!!!

[ahalliwell]

Glenn that is exactly what I want.

I have absolutely no idea how you did that...

[Glenn Kennedy]

You're welcome. If you have any Qs about it, once you try to implement this in your real sheet, just shout.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[ahalliwell]

I spoke too soon,

I didn't mention this before but I need to be able to count multiple cells now, and I don't think its as easy as changing the cell from "b3" to the other cell?

[Glenn Kennedy]

I'm away from the PC. What, exactly, do you mean by multiple cells? One result per cell, one result for n cells that are beside each other or one result for n non-contiguous cells, or so.ething else????

[Glenn Kennedy]

If it is just one other cell, change B3. IF You want something else it may be easy... but I need a good explanation.

[ahalliwell]

Well what I want is to be able to count the word available over what ever cells I want? The word 'available' shows based on a time to date grid so I have split each day of the month across the top and 8am 8pm downward for each month on 15sheets so in my first example B3 was the top left corner of the data in the 'january' table.

So technically it would need to cover 100's of cells?

[Glenn Kennedy]

1. How many times can available appear per cell?

2. If the range is A1 to B10 on each sheet (20 cells per sheet) Do you want 1 answer (covering 20 cells x n sheets), or one answer per cell (i.e 20 answers in total)?

You must accept that what you are asking for now is NOT the question you asked at Post 1....

[ahalliwell]

Hi,

I know well I tried to keep it simple, I assumed that if I could get the formula to count 1 cell I could simply use that code to then count what ever I wanted in what ever cell I wanted on whatever sheet I wanted.

I didn't realise that counting the amount of times a word appears in different sheets would be so complicated lol

so in essence if I have sheet 1 where I want the count to happen then Cell B3 would count the amount of times "available" is shown in cell B3 in the sheets of my choice, I know I've done Countifs when the words are on a single sheet and that works fine for me but as soon as I start going over sheets it doesn't seem to count the number of times it is displayed instead it just shows 1 when all the cells contain "available" and 0 if even one of them doesn't

[Glenn Kennedy]

You haven't answered EITHER of my questions. So, one last blind guess.

Enter any cell reference in B2. (It'll only deliver results for B1 to B6, though).

if that's not what you want, then...

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[ahalliwell]

Hi,

Sorry if it was not clear, its even simpler than your suggestion.

So I have a sheet called "availability" which has every day of the month split into hours running from 8:00am to 8:00pm.

In cell B3 is the day 01/01 and the time 8:00-9:00 so every month is broken up like this on each individuals sheets.

on the availability I want cell B3 to count each time every other cell B3 contains the word "Available", then I want cell B4 to count every time the cell B4 in the chosen sheets also says "available"

so in essence its quite a simple "countif" formula but going over sheets, it just doesn't seem to work, which is what I did enter into my very first post, I just left out the other cells in the hope that your answer would be replicable over different cells, I didn't realise how complicated it would be to count.

Below is what my form looks like which should kind of give you an idea of what I want, this is the code I tried (again I did enter this into my first post to try and simplify what I wanted)

I would enter the following code in sheet "availability" cell B3, this code would then be similar in every other cell

Please Login or Register  to view this content.

The only issue I had was that the above code only counted when all the boxes said "available" and not count how many times it says "available"

Here's the document:

[Glenn Kennedy]

In B3, copied across and down. I manually created a sheet list in sheets. It is fully dynamic, currently to a maximum of 99 sheets.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!"&ADDRESS(ROWS($1:1)+2,COLUMNS($A:A)+1,4)),"available"))

if you want to hide all the zeros, you can use:

=IFERROR(1/(1/SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!"&ADDRESS(ROWS($1:1)+2,COLUMNS($A:A)+1,4)),"available"))),"")

instead.

[ahalliwell]

Excellent!

Thank you for your help, you have fixed my problem!