Hi excel forum,
I am using the following formula to count across multiple worksheets
I was wondering it it was possible to add multiple criteria to this formula so it counts the cells indicated if 1 of the criterias it matched.
Please Login or Register to view this content.
So if E2 & E3 are the criteria and 1 of them is matched it counts how many.
If a example sheet is needed let me know.
Thanks in advance
A sample sheet would help!!
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.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Please see example workbook
Not to worry i worked it out myself, this seemed to work
If someone thinks they have a more simple way suggestions are appreciated.Please Login or Register to view this content.
Surely that only counts A1??
=SUMPRODUCT(COUNTIF(INDIRECT("'"&B3:B5&"'!A1:A4"),{"A","B"}))
Since you have Excel 2016:
Why not use Power Query to get all data on 1 sheet and after that analyse the data?
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
PowerQuery is as powerful as its name suggests, but is still very new or completely unknown to many users. It would help, therefore, if those 'in the know' would offer an explanation of how to solve problems like this with it when they see a use for it.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
@AliGW
I could do that, but the added examples give not much information to show.
In general:
My statement would be that, in most of the times, it is usefull to use just 1 sheet instead of a lot of worksheets.
With just one sheet it is a lot easier to analyse the data.
So even if a forummember don't have the Excel 2016 version I would recommend to built a file that way.
So what do you need from the OP to be able to demonstrate the PowerQuery solution? Many of us are keen to learn more about it.
More in general:
I use this code (a lot) to get all sheets on 1 summary sheet.
You have to accept macro's to run the code.
See the result in the attached file.
After that I will take a look if I can explain how this can be done with PivotQuery.Please Login or Register to view this content.
Last edited by oeldere; 09-24-2017 at 06:50 AM.
for the powerquery tool i will take a look if I understand how to add data of differant sheets together.
If so, I will post that.
If not, I will post that either.
If there are more experienced members on this topic, feel free to step in.
Try
=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!A1:A10"),D1:E1))
"Sheets" is named range of the list of sheets i.e, B3:B5
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks