COUNTIF function across columns, worksheets

Hi wernie,

Welcome to the forum.

Suggest you to upload a sample workbook to support your query. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

if you have the workbook, why not upload that instead of a picture? you may just get a picture back with your answer in it

see if this is what you need?

if you want to include workbooks in this, that could probably be done through indirect()

@FDibbins

Thanks - that looks like what I need to be doing! So then this leads me to a few questions (sorry, I am definitely not an Excel expert!):

- When you populated the cells did you have to type out those formulas, or can you just drag across rows/down columns with the way you've typed it out?
- I'm not quite sure what you mean by the workbooks and indirect (), but that may relate to my second question - when I create a new spreadsheet for the next set of records with new PD - is there a way to easily 'transfer' this so it will just automatically populate the cells based on those formulas for study area/age/gender?
- Because in one spreadsheet it may specify A2:A22, but another it might only be A2:A5, so is that possible to do so?

Thanks again, I really appreciate it! :D

to your 1st question. yes you can copy them down just like i have them. you may have noticed when you opened the file, i had a range high-lighted. this is a 3-row range for your age group - total, m,f. when you add more age ranges, copy these 3 rows together to the next range, they all reference the age group on the "total" row. so if you copy them all together, you wont need to mess with the references

regarding your 2nd question about the different workbooks, if you just intend to copy/paste that entire sheet, to another file, why not keep them all in the same workbook, and just use new worksheets (tabs) each time? that way, you can just copy the format each time and change the data.

doing it that way, you could even, if you wanted, have a summary of all sheets

if you really do want to create new workbooks (files) for each set of records, i "think" you should just need to copy/paste the tables each time. but i still think new worksheets would serve you better

Great, thanks! I am gonna have a play around with it and see how I go....I might end up back here with another question or two, but I'm excited to try this out.

Oops - I did mean new worksheets I'll have new worksheets for each new PD code within the same year but then new workbooks (new files) for the different years to help better sort all of the data.

ok great. shout if you need more help with anything.

1 thing i would suggest tho, is to try and keep the format (length/width) of each worksheet consistant - even if it goes down way below the data you have (in essence you will use a "template" for each new sheet). that way, if you want to do a summary, specific data will be in the same row/column/cell on each sheet, making a summary a breeze

yup thats exactly what i meant with multiple "similar" sheets, constancey if often the key to keeping things simple and easy to work with. if its a big issue with having too many open/blank lines on a particular sheet because your data doesnt go down that far...hide some rows

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)
if you still have questions regarding this thread, fire away

Thanks. So I suppose one more quick (and probably easy) question would be how would I then go about doing summary sheets from all of the sheets with the count totals that were created from that template?

Also, is there a way I can custom filter or sort so it will group/only show all codes under 'PD' in a certain range....for example - all codes that start with '740' or '81' etc. Because I need it to pick up 740, but then also codes like 7402, 74035, etc. Not sure if this should be a new subject, or if it is okay to post here Thanks!