Using the COUNTIF function across sheets in a workbook

This video shows how you can use data on one spreadsheet in the calculations of a different spreadsheet in the same workbook.

[@lizzylee2106]

THANK YOU!!!!!! You saved me at work today. You are a great teacher.

[@StephenRansom]

Thanks! Glad it helped.

[@dmack5205]

Thanks for posting this video. Very helpful.

[@StephenRansom]

dmack5205 Thank you. Glad to hear it.

[@NaviSharmaNav]

Thanks a lot. It was clear and very helpful.

[@StephenRansom]

Great! Glad to hear it was useful to you.

[@mahchannel1866]

Thank you very much. This video is very clear, concise, and very helpful.

[@clnatrbc]

Thank you so much!. This is exactly what I was searching for.

[@StephenRansom]

Glad it helped!

[@vanessajones3825]

What if you are doing something where you have two options for instance, L(number) and D(number) the l and d signify a different class. And I have a total column for l and then d Anyone using the spreadsheet can put in hours in the numbers place, and in the end I want the excel total to add the numbers and just put in the respective columns but can’t figure it out 😥. Say l1 then l4 so I would want the l’s to go in the l total part and display the number simply as 5 (1+4)

[@mofojackson]

Awesome vid thanks buddy! Going to check out more of your videos :)

[@roux6998]

Really clear thanks, however I wish to add a second range of criteria from a second sheet, I've duplicated the formula but it returns an error.

[@StephenRansom]

Don't be afraid to google your question. www.extendoffice.com/documents/excel/2540-excel-countif-multiple-criteria.html You might also want to look at =COUNTIFS exceljet.net/excel-functions/excel-countifs-function

[@emilywaelder5253]

So helpful! Thanks!

[@ed4253]

Very helpful, thank you.

[@StephenRansom]

Glad to hear it!

[@filmbc]

This is a great tutorial. I am creating something very similar to this only it is a report that other people will need to fill in. So, in the second tab where the data is being collected, I have drop down option in each row. How do I create a COUNTIF formula where the data has not been filled in yet. Or is this at all possible? Thank you!

[@StephenRansom]

Sure it's possible. Just make sure that your formula syntax includes the entire range of possible data, even if it isn't there yet. The countif function will count whatever is in that range, whenever it gets submitted/entered.

[@filmbc]

Thank you for such a quick response. Do you put "double quotes" around every single data entry? Commas as well? Like this: =COUNTIF('Patient Details'!D2:D200,"Moved""Financial/Cost""Not Ready""Emailed Patient") Also, on the first sheet I have a list of outcomes on each row. If the formula includes the entire range of possible data, then how will I be able to determine the number of times a certain piece of data was selected for that particular row? Hard to explain. Basically I have 21 outcomes the end user can chose from on the second sheet for each patient file that they audit. They are supposed to track this data on the second sheet and select and outcome for each file. I want that information to be tallied up on the first sheet for each outcome; i.e. moved, financial/cost, not ready, emailed patient, etc. Thoughts?

[@StephenRansom]

Something to note, you do not need the item to be counted in " ". Instead, you can just click on the cell reference that contains the item to be counted (as shown in this video). Now, typically, COUNTIF is used to count a single variable. You can combine different variables with with + , i.e. =COUNTIF( )+COUNTIF( )+... But, it sounds like you are actually wanting to use COUNTIFS to count multiple criteria in the same expression. Search KZbin for "countifs with multiple criteria" and see if that is more what you are looking for. I can't tell exactly by our description what you are wanting to do and if this function is the best way to do it.

[@filmbc]

Ok great! I certainly appreciate your help and fast response. I love Excel, but the learning curve is steep and trying to explain things is proving to be difficult. Thanks again.

[@StephenRansom]

Yes... much easier to show than explain. Good luck! The answer to "can Excel do this" is always yes. It's an incredibly sophisticated software application. KZbin and Google are your friends here :-)

[@venitacason9753]

How do I use the same formula to count across multiple worksheets though, i.e. Formula Response + Months for "September?"

[@StephenRansom]

You do it the same way as shown in this video, selecting the multiple ranges from the multiple worksheets that you want counted. Or if more appropriate, use the COUNTIFS function support.office.com/en-us/article/COUNTIFS-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842

[@auroraautumn8764]

Thank you

[@khsakir4550]

Many many thanks, this is exactly what i am looking for.

[@StephenRansom]

Great!

[@providenceconsultingdesign7837]

Is there a way to use COUNTIF to count across an entire book? So if I want to count how many times the response "Good Condition" was input across a 45-page excel book and populate the count on one page, how would I go about doing that?

[@StephenRansom]

Hunter Abrams www.mrexcel.com/forum/excel-questions/214542-countif-whole-workbook.html

[@mevartzon1374]

Ideal tutorial for any person who wants to learn excel formula. It's very helpful and informative. Can i use COUNTIFS with data validation suppose September here has one more column like status of September and status September is good or bad and i want to reflect or count only good September how many times not all September to be counted. How can i dp that?

[@StephenRansom]

Definitely possible. Not something I can really explain in a comment. You can combine countif functions... ie. =COUNTIF( )+COUNTIF( ), ETC.... But, if I am understanding you correctly, this is a basic countif problem. You just identify the column range of good/bad and count the criteria that you need (good or bad).

[@riteshrana2516]

Sir I need Datewise countif function

[@machbauer132]

If we would expand this task with sheets, let's say from 2010 till 2015, how should the function be modified?

[@StephenRansom]

I'm not sure I understand your question. This video shows how to use this function across sheets.

[@guillemramos6352]

Hey, thanks a lot for the video! Unfortunatetly, this doesn't work in my Excel (I followed your video step by step, and it just isnt' working). Do you think it might be because of me having an outdated Excel version? Thanks and see you soon!

[@StephenRansom]

Guillem, it shouldn't have anything to do with an outdated Excel version. It is amazing how you can have the smallest problem with your syntax which will stop a function from working. I would suspect this is the issue. It's very hard sometimes to see your own flawed syntax, especially when it can be as small as a comma, space or period.

[@guillemramos6352]

​@@StephenRansom Thanks a lot for your answer! I think it's actually due to the fact that Excel is not capable of applying the "COUNTIF" nor "COUNTIFS" functions to 3D ranges. At least, that's what I've been able to find out in the internet (both informal forums and the official Microsoft support page). Thanks anyways for your quick answer! :)

[@StephenRansom]

@@guillemramos6352 No problem. I'm not sure what your data set looks like, but perhaps you need to use a LOOKUP function? (VLOOKUP/HLOOKUP) or INDEX function?

[@Gilazel]

Hi, can i get some help ? Im getting a #VALUE error, that error should occour when the worktable is closed but i have it open for sure. Created a new workbook for testing and still geting the same error. Created the sheets just like yours to make sure i wasnt doing anything wrong. Edit: For a quick fix im doing a local countif function on every sheet then im using a sum function to sum all the countif sheets, that worked but still would like to know why the video method isnt working.

[@StephenRansom]

Sorry, that's a very general error... could be many things. In general, there is something wrong in the formula you typed or in the cells/sheets being referenced. There's no way I can be more specific that that without actually seeing your file.

[@heliodoroarzate9628]

Ok, so I am doing CountIF function for report I need, I need it to search multiple sheets but only certain columns, I manage to make it work for just 1 sheet, but when I add multiple sheets I get an #Value message

[@StephenRansom]

Check your syntax to make sure the formula is referring both the the column(s) and the sheet(s) in question.

[@villafontananorte]

Okay, but, how would you tell Google sheet to count how many times the wordn"september" appears in the last 2 weeks?

[@StephenRansom]

You count only in the range of the last 2 weeks.

[@sharaijaz]

how can I count similar from two or more sheets? like September from sheet 1 and sheet 2

[@StephenRansom]

Did you watch the video? That's what it shows how to do.

[@anthonyyung3387]

Thank you so much for sharing such invaluable excel course on youtube.

[@StephenRansom]

You're welcome!

[@herarana7673]

thank you

[@gishaniadhikari7694]

Oh! Thank you so much....

[@StephenRansom]

You're welcome. I'm glad it helped.

[@guillemramos6352]

What version of Excel are you using here?

[@StephenRansom]

I'm fairly certain it was 2010

[@racheltedford424]

I'm going crazy, how do I count how many people were either rejected or accepted for either a manager position or junior position on a specific day i.e. how many people were rejected for the junior position on 01 January? I've tried sooo many different formulas and none are working.

[@StephenRansom]

Nia, have you tried the COUNTIFS function? It allows you to use multiple criteria and ranges in a single argument. Or, you can also use =COUNTIF(...)+COUNTIF(...), etc... It's hard to give specific advice without knowing the exact scenario and data setup.

[@janelchay6591]

Thank you!

[@StephenRansom]

Glad you found it helpful!

[@kongvina7205]

can we use the COUNTIF function across two sheets ?

[@StephenRansom]

Kong Vina Yes, and even across workbooks.

[@zionguru4883]

I found that if I sorted the reference sheet the "function across sheets" would be out of order. I did this to remedy that: =countif('FB - Trips - Kane County BLM'!J$2:L$64,"Birch Hollow")

[@lg781]

WHat if I wanted to use data from multiple sheets?

[@StephenRansom]

That's what this video is showing. It doesn't matter what sheet... or even what workbook, for that matter, the data resides on.

[@Gio626100]

THANK YOU SO MUCH!!!!!

[@StephenRansom]

So glad it helped!

[@irishraychellemanlunas4461]

=COUNTIF('FEBRUARY'!K14:K21, 'FEBRUARY'!K19) what's wrong with the formula? please help

[@StephenRansom]

I don't see anything wrong with it, but without the workbook, it's hard to troubleshoot.

[@alaminsarker4558]

Hi. I need a formula. Can you help me?

[@StephenRansom]

support.microsoft.com/en-us/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb

[@alaminsarker4558]

@@StephenRansom i need to counting formual

[@readerbranch4431]

COUNTIFS WITH TWO NESTED VALUES\

[@jpapauran95]

Thank you