Excel - COUNTIFS with multiple criteria and OR logic

Поділитися
Вставка
  • Опубліковано 26 сер 2024
  • Excel - COUNTIFS with multiple criteria and OR logic
    IF statement to add/deduct points from average
    When you use multiple criteria with OR logic, caution is required so that you don't double-count. In some situations it can be easy to accidentally double count by applying criteria that are too loose. I'd suggest doing a double check on a couple of rows (or columns) of data to verify accuracy.

КОМЕНТАРІ • 81

  • @tim3666
    @tim3666 2 місяці тому

    Unbelievable, I have been struggling with this all day. Thank you Alan.

  • @carmenicas
    @carmenicas 4 роки тому +5

    This is the best thing in the world. THANK YOU!

  • @dontrimp1664
    @dontrimp1664 10 місяців тому +1

    Thanks so much ❤ it worked and in such less time😅 great vid🎉

  • @12131948
    @12131948 4 роки тому +3

    Thank You so much for this!!! This exactly what i have been trying to accomplish for weeks !!

    • @bman342a
      @bman342a 2 роки тому

      Same here. Not sure why this is not easier to accomplish.

  • @grubershmuel
    @grubershmuel 2 роки тому +2

    Hi I tried to use the countif function like you did in this video. Then I tried to define specific columns for the function but excel wouldn't let me select more than 3 ranges of columns. Is there a function that will allow me to get around this problem and be able to select and count words in multiple columns?

  • @gersoncastillo8923
    @gersoncastillo8923 4 роки тому +1

    Thank you so much. SUM before countifs and the curly brackets. Genious

  • @mihailshevchuk9215
    @mihailshevchuk9215 Рік тому

    that dude is simply the best, many thanks to the universe that you man exist 😎 🍻🥳

  • @rinaldirahardja8449
    @rinaldirahardja8449 Місяць тому

    I have a sheet like this but in my case: there are 3 Smiths (the name) category (i.e biology, math, and language). I would like to get the count each unique row (count the "x") for example smith language has three row of absence in this sheet (3 rows which has "x" to count). Please enlight me brother

  • @yazminghale2285
    @yazminghale2285 3 роки тому

    Thank you soo much it helped me alotzz though I was nt interested in computer back then in school lyf I had to do now bcoz of the job I have …. thanks

  • @juliusyorke904
    @juliusyorke904 Рік тому

    Just what I was looking for. Thanks Alan

  • @addliam
    @addliam 2 місяці тому

    Perfect! Just what i needed

  • @user-cy3mr3ir4c
    @user-cy3mr3ir4c Рік тому

    Hi, at 3:27 of the video, does the bracket need to be {"abs","late","ills"} or can the bracket be ("abs","late","ills")?

  • @m.n.953
    @m.n.953 2 роки тому

    thanks alot
    i have in shifts shudle rota that has in row 6 the dates and my imployees works some times in weekends . how could i count by formula the days they work on weekends for trible fee. i wrote this formula but it dosent worked : my imployyes names start at A8 to A15 and the letter W is for work day ; and the dates from B6 to AF6: countifs( C7:AF7,"=W", B6:AF6,"weekday($B$6,1)>5")
    our weekend is friday and saturday.
    so what is wrong with my formula and how it should be ?
    thanks in advanced

  • @Steave-M
    @Steave-M 4 роки тому

    You are amazing, you saved my life with {...} Thanks a lot we need more videos like this please

  • @ManiacGamerMo_loud
    @ManiacGamerMo_loud 5 років тому +2

    Can I do it with multiple ranges rather than criteria? It would useful for me and thanks.

  • @eiggknight
    @eiggknight Рік тому

    What if the rows have a drop down list? How would the formula change? Thanks

  • @jaymanuel3940
    @jaymanuel3940 3 роки тому

    Deyymm. You saved me bro. Thanks for this

  • @Richard-mq7wf
    @Richard-mq7wf 9 місяців тому

    how did you get the FUNCTION ARGUMENT window to show up?

  • @blackrain226
    @blackrain226 Рік тому

    Hello! How do you make to show you the dates that they were absent, for example? I really need this formula and I don't know how to write it, thank you!

  • @shabanashaik5265
    @shabanashaik5265 Рік тому

    How did you create Column A static column

  • @InspiringLeadersManish
    @InspiringLeadersManish 3 роки тому

    Thank You so much, was missing the curly brackets.

  • @bravo6quotes
    @bravo6quotes Рік тому

    You are a legend, I subscribed

  • @aslanbarisim
    @aslanbarisim 4 роки тому

    Thanks for your very helpful video ...

  • @fitranawawiinova9054
    @fitranawawiinova9054 2 місяці тому

    you save my life, thank youu

  • @Quotes4men
    @Quotes4men 3 роки тому

    Do you know if for google sheets is different? It's not working for me

  • @deborahv.3402
    @deborahv.3402 4 роки тому

    Is there a way to highlight the countif results in the original data?
    EG: Smith missed the Oct 11th then that day would be highlighted in the table based on the countif criteria

  • @mohammadsaifulislam6748
    @mohammadsaifulislam6748 4 роки тому

    Its Really Amazing!! I'm just looking for like this. Thank you so much.

  • @aysinsoytekin5313
    @aysinsoytekin5313 3 роки тому

    Thank you for this video! It is very very helpful!

  • @racheltedford424
    @racheltedford424 3 роки тому

    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.

  • @sanaullahboys92
    @sanaullahboys92 3 роки тому

    How I can get final average in this
    Please tell me

  • @sanapalagurunadharao1855
    @sanapalagurunadharao1855 2 роки тому

    If row counting wrong by automatic how should correct

  • @visothdul5760
    @visothdul5760 5 років тому

    I wonder if I want to count like this.
    Example
    1. 25
    2. 54
    3. 45
    4. 35
    I want to know how many times between 20 to 30... how many 30 to 40 and so on....
    how can I count like this. Please help me out

  • @user-cy3mr3ir4c
    @user-cy3mr3ir4c Рік тому

    Hi, at 3:27 of the video, do we need to place sum() around countifs(B7:AD7,{"abs","late","ills"})? Can we just type countifs(B7:AD7,{"abs","late","ills"})?

    • @jenniferdustin8214
      @jenniferdustin8214 9 місяців тому

      Without the sum, you get an array formula, that will spill. Essentially, its counting each of the items in brackets separately, the sum, adds those together to get you the total count.

  • @mujmilshaikh1
    @mujmilshaikh1 3 роки тому

    Thanks, it's very useful

  • @preetibang3400
    @preetibang3400 2 роки тому

    This formula is woking in Excel, But not in Google sheets. How can I make it happen in google sheet ?

  • @Evolve816
    @Evolve816 3 роки тому

    Soooooo does anyone know how to count values only once for this equation and not count duplicate values?

  • @daianamedrea6770
    @daianamedrea6770 7 місяців тому

    Thank you so much!

  • @Krishdigital
    @Krishdigital 6 років тому

    multiple sheet same range same criteria how do it

  • @rajvirsingh4558
    @rajvirsingh4558 2 роки тому

    Good Upload 👌

  • @vanessajones3825
    @vanessajones3825 4 роки тому

    Hi! what if you have for example a "key table" L or C then a number that is a free form. Can I email you what I'm talking about? I really need help :(

  • @rahuldas5969
    @rahuldas5969 5 років тому

    Too good man.. Thank you very much

  • @joannacortes1026
    @joannacortes1026 3 роки тому

    How to include the blanks??

  • @GohardashtBoy
    @GohardashtBoy 3 роки тому

    That was great. Thank you!

  • @dawnoon3029
    @dawnoon3029 Рік тому

    it helps, thank you :)

  • @samuelkodjoe1645
    @samuelkodjoe1645 5 років тому

    HELL YEAH!!! Y'RE A GENIUS!!!.....THANKS

  • @pemaseldon7715
    @pemaseldon7715 3 роки тому

    Please can u share in google sheet too. Since this doesn't work in google

  • @anooppvr85
    @anooppvr85 Рік тому

    Thank you Sir!

  • @fred0447
    @fred0447 5 років тому

    To relate this to my problem: Final averages are in a named range, within that range I wish to know how many Final averages are greater than 75 but less than 95. Thanks.

    • @alanbweaver
      @alanbweaver  5 років тому

      A pivot table could be the solution in this case. Unsure how to do this in ranges!

  • @suzanajacob5656
    @suzanajacob5656 2 роки тому

    Thank you!!!

  • @sowmiyamanoharan432
    @sowmiyamanoharan432 3 роки тому

    Thank you so much...

  • @satbtp
    @satbtp Місяць тому

    Thanks

  • @TheStephen005
    @TheStephen005 4 роки тому

    Thank u very much sir; yaah got it; really happy;🤩

  • @jaguarprovo
    @jaguarprovo 5 років тому

    U son of a gun! U helped me tremendously

  • @vishaljagtap8996
    @vishaljagtap8996 2 роки тому

    Hi its amazing but what if i use two arrays for example =SUM(COUNTIFS(Y:Y,{"5,000 - 9,999","10,000 - 19,999"},Q:Q,{"Denmark","Finland","Iceland","Norway","Sweden"})) i am not getting exact result can you help ?

    • @alifhanafi5536
      @alifhanafi5536 Рік тому

      same bro, I don't know why when I use {"24-28"} it doesn't work, but when I try {"24", "25", "26", "27", "28"} it work

  • @SanjayKumar-qh8no
    @SanjayKumar-qh8no 2 роки тому

    Is it possible formula for count of RAM and HARI =sum(countifs(A1:A12,("RAM","HARI")))

    • @prakharyadav5279
      @prakharyadav5279 Рік тому

      It should have Curly Braces "{}" instead of small braces "()" before "Ram","Hari"

  • @cr7rulz97
    @cr7rulz97 3 роки тому

    Thanks a ton!

  • @rahuls024
    @rahuls024 3 роки тому

    i want to count the words like ("PL","HOL","CFL") appearing in a row if the date on the other row is greater than today()
    also if the date is less than today()

    • @rahuls024
      @rahuls024 3 роки тому

      i want to count the words like {"PL","HOL","CFL"} as 1 and {"HD","CFL-HD" } as .5 appearing in a row if the date on the other row is greater than today()
      also if the date is less than today()

  • @mayanksharma-ol1sf
    @mayanksharma-ol1sf 2 роки тому

    thank you so much

  • @malikazeem4213
    @malikazeem4213 5 років тому +1

    Thank you dude

  • @eloisaigna
    @eloisaigna 4 роки тому

    Thank you!

  • @Smybsi
    @Smybsi 6 років тому

    Hi What if i need to formulate an excel with these requirement. the value to be lesser than 2, bigger than 2, lesser than 40 not including 2 , bigger than 40,lesser than 100 but not including 40. or lesser than 100000 but not including 20 30 40 100. or total count not inclusive 40 60 80 100.

    • @alanbweaver
      @alanbweaver  6 років тому +1

      I don't know the answer to this yet I would suggest using a multiple level IF statement

  • @anggawibi5170
    @anggawibi5170 3 роки тому

    Thanks a lot

  • @harithapadmadas8815
    @harithapadmadas8815 4 роки тому

    Why isn’t this working for me in my google sheets ! 😢

  • @dinahany233
    @dinahany233 3 роки тому

    Thanks 😊

  • @reddisunitha4708
    @reddisunitha4708 3 роки тому

    This is what I want

  • @faridi-bin-Zouq
    @faridi-bin-Zouq 4 роки тому

    good

  • @richardyoung3555
    @richardyoung3555 4 роки тому

    really appreciate your {} !!!!

  • @lauraanninos3445
    @lauraanninos3445 6 років тому

    Vs