CountIF Across Different Sheets

Not totally simple, but very do-able. Sheet references apply to my sample file.

1. Save your file as macro enabled.

2. Add a Named Range (CTRL-F3) called Sheets, referring to =TRANSPOSE(GET.WORKBOOK(1))&T(NOW())

3. Add another named range, called Sheetlist, referring to =OFFSET(Master!$E$2,,,SUMPRODUCT(--(LEN(Master!$E$2:$E$100)>0)))

4. use this array formula in Master E2. copy down to row 100.

5. Use this formula in Master!A1: =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheetlist&"'! A1"),1))

So, if there are more than 100 sheets, adjust the 100 in the formula at 3) and copy the formula at 4 down further.

that's it. See file. if you andd another sheet, it'll add to the sheet list and, if A1=1, will be counted at Master!A1.

Don't forget to enable macros when you open this file....

Dohh!

i forgot to add:

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not 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 braces yourself - it won't work...

Davsth... very true!! However, as you said, your suggestion is so limited in its application; I went for a more general solution that'll work with all sorts of data.

It won't (can't) work with countif as it does not accept 3D references.

I now have the sheet fully set up for you. Rules:

1. Add on the individual response sheets to the right of the last response sheet.

2. Do not move any of the other sheets to the right of ANY of the individual response sheets (you can probably hide them to prevent someone moving them by mistake).

3. Think carefully. Will you need to add any more sheets (other than individual response sheets) to this? If so, DO SO NOW and re-post as one of the named ranges will need to be tweaked...

4. Play with it and check it is giving the responses YOU expect. It's giving the responses that I think you expect!!!

5. Enable macros on opening.

happy to explain...

You're right.... There was an extra sheet on your posted sample (I'm on the phone now & can't remember what it was called). I deleted it and forgot to change the named range that uses the sheet list. I can tell you what changes to make; but kudos for figuring out a workaround!

Regarding Guinness: at home I stick to my own bottled IPA. Off to the home of the black stuff for a couple of days tomorrow morning to go to a couple of gigs in some of Dublin's music clubs. I'll have had more than enough Guinness by Friday...

Glad to have helped & thanks for the Rep (Guinness substitute).

You have inserted two new columns into SOME of your data sheets (e.g. 1002,1003,1004,1005). (Columns B & C) with some sort of date-related function in them. You can't do that. The formula is not (will never be) clever enough to work tat out. Up to the column where the last of the data exists the column structure MUST be identical. So, if you do need those new columns, move them off to the right somewhere.

You're welcome. The good thing is I remembered the question and didn't have to waste a age figuring out what I'd done....

---

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.