Using the COUNTIF function across sheets in a workbook

แชร์
ฝัง
  • เผยแพร่เมื่อ 23 ธ.ค. 2024

ความคิดเห็น • 73

  • @lizzylee2106
    @lizzylee2106 4 ปีที่แล้ว +5

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

  • @sharaijaz
    @sharaijaz 4 ปีที่แล้ว

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

    • @StephenRansom
      @StephenRansom  4 ปีที่แล้ว +1

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

  • @providenceconsultingdesign7837
    @providenceconsultingdesign7837 5 ปีที่แล้ว

    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
      @StephenRansom  5 ปีที่แล้ว

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

  • @Gilazel
    @Gilazel 4 ปีที่แล้ว

    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
      @StephenRansom  4 ปีที่แล้ว

      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
    @heliodoroarzate9628 4 ปีที่แล้ว

    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
      @StephenRansom  4 ปีที่แล้ว

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

  • @lg781
    @lg781 3 ปีที่แล้ว

    WHat if I wanted to use data from multiple sheets?

    • @StephenRansom
      @StephenRansom  3 ปีที่แล้ว

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

  • @guillemramos6352
    @guillemramos6352 3 ปีที่แล้ว

    What version of Excel are you using here?

    • @StephenRansom
      @StephenRansom  3 ปีที่แล้ว

      I'm fairly certain it was 2010

  • @machbauer132
    @machbauer132 5 ปีที่แล้ว

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

    • @StephenRansom
      @StephenRansom  5 ปีที่แล้ว

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

  • @venitacason9753
    @venitacason9753 7 ปีที่แล้ว

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

    • @StephenRansom
      @StephenRansom  7 ปีที่แล้ว +1

      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

  • @filmbc
    @filmbc 7 ปีที่แล้ว +1

    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
      @StephenRansom  7 ปีที่แล้ว +1

      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
      @filmbc 7 ปีที่แล้ว

      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
      @StephenRansom  7 ปีที่แล้ว +2

      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 TH-cam 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
      @filmbc 7 ปีที่แล้ว +1

      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
      @StephenRansom  7 ปีที่แล้ว +1

      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. TH-cam and Google are your friends here :-)

  • @mevartzon1374
    @mevartzon1374 4 ปีที่แล้ว

    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
      @StephenRansom  4 ปีที่แล้ว

      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).

  • @villafontananorte
    @villafontananorte 6 ปีที่แล้ว

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

    • @StephenRansom
      @StephenRansom  6 ปีที่แล้ว

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

  • @roux6998
    @roux6998 4 ปีที่แล้ว

    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
      @StephenRansom  4 ปีที่แล้ว +1

      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

  • @guillemramos6352
    @guillemramos6352 3 ปีที่แล้ว

    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
      @StephenRansom  3 ปีที่แล้ว

      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
      @guillemramos6352 3 ปีที่แล้ว

      ​@@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
      @StephenRansom  3 ปีที่แล้ว

      @@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?

  • @vanessajones3825
    @vanessajones3825 4 ปีที่แล้ว

    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)

  • @kongvina7205
    @kongvina7205 4 ปีที่แล้ว

    can we use the COUNTIF function across two sheets ?

    • @StephenRansom
      @StephenRansom  4 ปีที่แล้ว

      Kong Vina Yes, and even across workbooks.

  • @riteshrana2516
    @riteshrana2516 5 ปีที่แล้ว

    Sir I need Datewise countif function

  • @anthonyyung3387
    @anthonyyung3387 4 ปีที่แล้ว +1

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

  • @racheltedford424
    @racheltedford424 4 ปีที่แล้ว

    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
      @StephenRansom  4 ปีที่แล้ว

      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.

  • @dmack5205
    @dmack5205 7 ปีที่แล้ว

    Thanks for posting this video. Very helpful.

    • @StephenRansom
      @StephenRansom  7 ปีที่แล้ว

      dmack5205 Thank you. Glad to hear it.

  • @alaminsarker4558
    @alaminsarker4558 3 ปีที่แล้ว

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

    • @StephenRansom
      @StephenRansom  3 ปีที่แล้ว

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

    • @alaminsarker4558
      @alaminsarker4558 3 ปีที่แล้ว

      @@StephenRansom i need to counting formual

  • @NaviSharmaNav
    @NaviSharmaNav 5 ปีที่แล้ว +1

    Thanks a lot. It was clear and very helpful.

    • @StephenRansom
      @StephenRansom  5 ปีที่แล้ว

      Great! Glad to hear it was useful to you.

  • @mahchannel1866
    @mahchannel1866 9 ปีที่แล้ว +1

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

  • @clnatrbc
    @clnatrbc 6 ปีที่แล้ว +1

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

  • @khsakir4550
    @khsakir4550 5 ปีที่แล้ว +1

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

  • @ed4253
    @ed4253 5 ปีที่แล้ว +1

    Very helpful, thank you.

  • @irishraychellemanlunas4461
    @irishraychellemanlunas4461 4 ปีที่แล้ว

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

    • @StephenRansom
      @StephenRansom  4 ปีที่แล้ว

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

  • @emilywaelder5253
    @emilywaelder5253 9 ปีที่แล้ว +1

    So helpful! Thanks!

  • @mofojackson
    @mofojackson 7 ปีที่แล้ว

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

  • @auroraautumn8764
    @auroraautumn8764 6 ปีที่แล้ว +1

    Thank you

  • @gishaniadhikari7694
    @gishaniadhikari7694 6 ปีที่แล้ว

    Oh! Thank you so much....

    • @StephenRansom
      @StephenRansom  6 ปีที่แล้ว

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

  • @zionguru4883
    @zionguru4883 7 ปีที่แล้ว

    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")

  • @Gio626100
    @Gio626100 4 ปีที่แล้ว +1

    THANK YOU SO MUCH!!!!!

  • @janelchay6591
    @janelchay6591 8 ปีที่แล้ว

    Thank you!

  • @readerbranch4431
    @readerbranch4431 4 ปีที่แล้ว

    COUNTIFS WITH TWO NESTED VALUES\

  • @herarana7673
    @herarana7673 5 ปีที่แล้ว

    thank you

  • @jpapauran95
    @jpapauran95 3 ปีที่แล้ว +1

    Thank you