How To Count Items Across Multiple Worksheets In Excel

[@dhirajpandey6018]

It's because of people like you things become so easy for some one like me, thankyou for sharing it, it was bang on what I needed🎉

[@ExcelBytes]

Thanks for the kind words. I'm glad this was useful for you.

[@Up4Excel]

Very interesting formula Mike (and also thanks to Dave). I've never thought of using INDIRECT like this to reference multiple sheets simultaneously 👍

[@7208676934]

Hi Mike, Your tutorial helped me a lot. Thanks for making such knowledgeable videos. Love from INDIA...

[@michaelrempel2905]

Thanks. I'm glad you found it useful.

[@jillsmyth]

Very helpful. Exactly what I was looking for. Thanks!

[@ExcelBytes]

I'm glad you found it useful!

[@bibotheghost]

You helped me so much thanks ❤️❤️

[@ExcelBytes]

Great, glad it helped.

[@KCGray]

You are awesome!!! Thank you!!!

[@ExcelBytes]

Thanks. Glad you found this useful.

[@wayneedmondson1065]

Hi Mike.. Happy New Year! Thanks for kicking it off with this interesting formula and technique. Thumbs up!

[@jhonaviealloso6199]

Thank you so much! This was very helpful! Exactly what I was looking for. More power to you!

[@ExcelBytes]

I'm glad you found it useful!

[@jhonaviealloso6199]

@@ExcelBytes but how do I change the number of sheets to be counted? for example how will I count only 3 sheets instead of the original 4 sheets?

[@ExcelBytes]

@@jhonaviealloso6199 in the video you can see that we named the 4 sheet names in cells A4:A7 as "Sheets". You can include or exclude any of your worksheets when you create that named range

[@jhonaviealloso6199]

@@ExcelBytes will it then be automatic when i change the range?

[@ExcelBytes]

@@jhonaviealloso6199 Please explain what you mean by changing the range?

[@PoMkAc27]

Oh, thank you very much. You help me a lot. 🎉 do you know how to make it with COUNTIFS according to two criteria?

[@PoMkAc27]

Sorry. I understood it =SUMPRODUCT(COUNTIFS(INDIRECT(“‘“&sheets&”’!”&”H:H”);B34;INDIRECT(“‘“&sheets&”’!”&”J:J”);A55)) H:H - range for first criteria, B34 - first criteria, J:J - range for second criteria, A55 - second criteria

[@ExcelBytes]

@@PoMkAc27 =SUM(COUNTIF(INDIRECT("'"&Sheets&"'!"&"A1:E10"),A9),COUNTIF(INDIRECT("'"&Sheets&"'!"&"A1:E10"),A10)) where A9 is first criteria, A10 is second.

[@niallgriffiths5387]

Great Video, just wondering would spaces in words affect the functions outputs as I dont seem to be getting the correct numbers?

[@ExcelBytes]

No. There may be some other issue with your formula. If you'd like me to look at it, feel free to send it to [e-mail] and I'll see what the issue is.

[@LLUHPsychiatry]

Solved! thanks so much! Not working for me, but I feel like I'm so close! anything you see wrong here? =sumproduct(countif(indirect("'"&Sheets&"'!"&"D5:D29"),B12)) Sheets being my sheet name summaries, and B12 the name i want to find "Psych Clerkship"

[@ExcelBytes]

There doesn't seem to be anything wrong with your formula, so it's got to be something else. If you want to send a copy of your file to me I'll be happy to take a look at it. Send it to [e-mail].

[@LLUHPsychiatry]

thank you so much, sending now. @@ExcelBytes

[@ravindra-shah]

Very interesting formula. My question is what if I have to drag the formula. Say, I have sheets from JAN, FEB....and so on and in the summary sheet I put the formula for range A1:G1 and then drag to get A2:G2 and so on...please help

[@ExcelBytes]

You need to have the range be a relative cell reference as you copy it down. See this tutorial: kzbin.info/www/bejne/mqG6g6qmg6l4h6s. The INDIRECT portion would need to be something like INDIRECT(CELL("address",A1)&":"&CELL("address",G1))

[@kelseyrichard2300]

Mike, I am currently trying to set up a formula/function that will reference the current month with the month in a cell. If the current month is equal to the month in the cell, I need it to show up on my "To Do" list. I know you have a video on how to make a todo list. This is for my garden to remind me each month what I should be cutting/trimming/planting. Does this seem possible? TIA

[@ExcelBytes]

We should be able to come up with something. Can you send me a sample workbook of what you have and what you want it to do and I'll take a look at it. Send to [e-mail]

[@3rdDog173]

I made a workbook for a dinner we are having.. I used a drop down list to show the menu. I used a =COUNTIF(‘1_30’!D:D,A1) which worked great to keep count of the specific items, but my problem is that the sheet has grown to 10 sheets laid out the same way. Now I want to be able to count all the items through all the sheets.. can you help me? I have a separate sheet with a table that has the sheet list how can I put it all together

[@ExcelBytes]

I'm happy to help. If you want to send me your file and explain where you want the results, I'll set it up for you. Send it to [e-mail]

[@kacijackson1234]

Hi. Thank you for the video, it is helpful. I am at 4:33 and trying to figure out what you are referencing for range A1:E10. Is that the current page you are on? What does that range identify?

[@kacijackson1234]

Nevermind :) You are referencing the original table.

[@kevinkeenan3457]

Great Video! Is there a way to make the A1:E10 dynamic? I am trying to count A1 cells in A1, B1 in B1, AA22 in AA22, etc. When I drag the formula on the "Totals" sheets it only sums the A1.

[@ExcelBytes]

Change the formula to something like this: =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!"&CELL("address",A1)),$A$9))

[@kevinkeenan3457]

@@ExcelBytes Spot on!, Tried using the address function, but cell is clearly better!

[@sianedwards376]

Hi Mike - thank you for this. I just have a quick question - I'm trying to total up the number of times a score has been given to 21 individuals who each have their own tab, ie in cell B19 how often does the score 3,2,1 appear. I've followed your formula but when it comes to the 'range' option what to I put in?

[@ExcelBytes]

If I understand your question correctly, can't you modify the formula to something like this: =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!"&"B19"),"3,2,1")). If that's not what you are talking about, feel free to send me a copy of your file with a detailed explanation of what you want to do, to [e-mail].

[@sianedwards376]

@@ExcelBytes Thanks Mike I'll give it a go and come back to you if I'm still stuck :)

[@raulgutierrez8112]

Hi Mike how would i change the formula to be able to use countifs instead or countif. I've tried playing around with this but unable to get it right. Thanks

[@ExcelBytes]

Do you have multiple criteria or only one?

[@ExcelBytes]

If only one, like this: =SUMPRODUCT(COUNTIFS(INDIRECT("'"&Sheets&"'!"&"A1:E10"),A9))

[@raulgutierrez8112]

It would be mutliple criteria always found on column E and the other on column d

[@ExcelBytes]

@@raulgutierrez8112 =SUMPRODUCT(COUNTIFS(INDIRECT("'"&Sheets&"'!$D$2:$D$110"),"criteria",INDIRECT("'"&Sheets&"'!$E$2:$E$110"),"criteria2"))

[@0rijinal]

On Google Sheets I copied the formula, but it only counts the first sheet. So I downloaded yours and it still only counts the first sheet. I only get a return of 6 on yours. Is there a setting? Thanks.

[@ExcelBytes]

It's not a setting issue, there is something else amiss. If you want to send me a copy of what you are having a problem with, I will look at it. Send to [e-mail]. As far as Google Sheets, I am not very familiar with those. Someone else recently had a similar issue with Google Sheets. If you want to send me a link to your Google Sheet I will also take a look at it.

[@JeffFischer342]

@@ExcelBytes Hi, I was having a similar issue with Google Sheets...did you figure anything out...

[@ExcelBytes]

@@JeffFischer342 I looked back in my files and couldn't find anything from the question 4 months ago, but if you want to send me a link to your sheets or an Excel sheet to [e-mail], with an explanation of what you are trying to accomplish, I will take a look at it.

[@ranboutros]

Hello, thank you this is very helpful! I am trying to do this, but it didn't work for me. This is my formula, anything wrong here? =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!"&"A1:A5"),A11)). In A11 i have my variable. I also have a defined range called sheets for my 2 tabs and i have in each tab between A1 and A5 names i want to count the number of times they appear overall. Later the list will be longer, i am trying simple for now. Also how does it know that the defined range is actually about tabs?

[@michaelrempel2905]

There could be a couple things. Can you send me a copy of your workbook to [e-mail]

[@ranboutros]

@@michaelrempel2905 i just did thank you!

[@kacijackson1234]

Hi again. Final question. Mine appears to only count the occurrences in the first sheet. Any idea why that might happen?

[@ExcelBytes]

Could be a couple things. Did you create a named range of all your worksheets? did you type the names all correctly? Did the worksheet names have spaces and did the named range put in underscores to replace those spaces? Easiest thing is to send me a copy of your workbook so I can look at it, or at least a copy of your formula. Send to [e-mail]

[@kacijackson1234]

@@ExcelBytes Thank you for replying. Yes, I've completed all of the points you mentioned. I'm going to comb through it once more and if I cannot get it to work, I will touch back base as you've directed.

[@anthoniparuchuri8118]

@@kacijackson1234 Hey did you ever figure this out? I'm having the same issue.

[@saraj0179]

Hi Mike, thank you for the demonstration. I believe this formula could be what I'm looking for. However I have my list of Worksheets, named range of Sheets, but I keep getting a #Ref! for the formula. My Worksheet names are numerical, not text. Does that require an adjustment? =SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!A1:J100"),A2))

[@ExcelBytes]

Is named range of your worksheets "Sheets" or "sheets"? That little difference may be the problem? Also, check to make sure that you don't have any extraneous spaces in either your worksheet names or the list of worksheet names in your named range. Other than those suggestions, feel free to send me a copy of your workbook to [e-mail] and I'll look at it.

[@saraj0179]

@@ExcelBytes Thank you for the quick response! I tried it both ways, but I will check the spelling again and check for spaces. If I'm still getting an error I'll send it to you. Really appreciate the offer of help!

[@saraj0179]

@@ExcelBytes I checked the list name and it was correct. I have emailed you a copy of my spreadsheet. The email is titled 'SumProduct Formula Issue'. Thank you for your time.

[@HarryDocker]

Is there a limit as to how many other worksheets you can reference? I'm trying to reference 30 worksheets but it won't let me do more than 10

[@ExcelBytes]

I don't know the answer to that, but I just did a test with 16 sheets and it worked fine. Is it possible that maybe one of your sheet names is spelled differently in the list than the actual sheet name? You can send me a copy of what you have and I'll take a look at it. Send to [e-mail]

[@HarryDocker]

@@ExcelBytes had another look at it this morning with some fresh eyes. It seemed one of the sheets had a space after the name once that was removed worked a treat

[@michaelrempel2905]

@@HarryDocker That's what I suspected. I'm glad you figured it out.

[@eyalruby]

how can i call all my sheets by one name?

[@ExcelBytes]

I'm not sure I understand your question. In the tutorial, I've named the list of worksheets by one name - "Sheets". If that's not what you are talking about, can you please explain more. Feel free to send a sample or better explanation to [e-mail]

[@libyaticketing8131]

PLEASE HOW COULD I MAKE A REFRENCES OF ALL MY SHEETS ? CAN YOU HELP PLEASE !

[@ExcelBytes]

Type the names of the worksheets exactly as they are on each worksheet like I did in the video. Then highlight the list of names. While they are highlighted, go up to the Name Box (where you see the work Sheets on the video) and type in the name you want to call the list. Make it simple and just use Sheets like I did.

[@selorm]

SOLVED: This formula won't work if the sheet within your named range hasn't been created yet - is there any way to error-proof this? I want to cry, I keep getting REF errors, is it possibly because it would be sum/countifing a string of text which is coming from a formula? (A RIGHT(LEN SEARCH formula). Yes, I can't count based on the values coming from this formula, but it's not the source of my issue. I swapped out my named range for just 1 sheet name and it worked. Why does it not recognise my named range? Is it because it's within a table? No... I just made a named range outside of the table. Is it because the named range isn't on the same sheet as the formula? No... didn't work. It's the named range causing this issue but I don't see anything I have missed in the formula..... =SUMPRODUCT(COUNTIF(INDIRECT(" ' " &TesterRange& " '! " &"C2:C500"),H2)) Is it because on every row, it is counting against different criteria (within the same range)?

[@ExcelBytes]

Not that I can think of. Glad you figured it out.

[@irishdawn8387]

wish you would have actually showes up real examples

[@JoshuaJEnriquez]

I get 0

[@ExcelBytes]

If you want to send me a copy of your workbook I will look at it. Send to [e-mail]

[@RacingMeyer8]

Too small and blurry

[@michaelrempel2905]

Sorry it's not working well for you. What medium are you using to view it? I've watched it on my PC monitor and on my Android phone and both are properly sized and quite clear.

[@RacingMeyer8]

@@michaelrempel2905, I'm using a galaxy s5 neo. on youtube via chrome. Oddly, it is hard to make out the data within the boxes. Am I the only one with this issue maybe?

[@razzypants]

Does this work with wildcards? I am trying to get it to count if the cell contains text, and for some reason it just returns 0. Here's the formula: =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!"&"G13:I13"),"*"))

[@ExcelBytes]

That formula should work. I tested it and I get the correct answer. Is your range right? Did you name the list of worksheets correctly? If you want to send me a copy of your workbook I'll take a look at it. You can send it to [e-mail]