How To Use Excel COUNTIFS With Multiple Criteria And OR Logic || Excel Tricks || dptutorials
HTML-код
- Опубликовано: 19 мар 2020
- In this tutorial let us see How To Use Excel COUNTIFS With Multiple Criteria And OR Logic
Click here to download the excel workbook: drive.google.com/file/d/1Flak...
We all know that we use the countifs function to count values matching with multiple criteria.
By default this countifs function considers the AND logic only. But what if you want to match with OR logic.
Let us see this by considering an example like this.
This is the sample data where I have entries with different statuses like this
and let me try to count the entries matching the criteria here on the right side.
1st let me count the entries whose status is “Complete”. I type the formula in cell G5 as
= COUNTIFS($C$5:$C$12,”Complete”) and enter to see the result as 3.
And the let me count the entries whose status is “In Progress”. Type the formula in cell G6 as
=COUNTIFS($C$5:$C$12,”In Progress”) and enter to see the result as 2.
Now, observe carefully. If we want to count entries whose status is either complete or in-progress.
We generally add both the above two results. But there is a formula to do this, type the formula in cell G7 as
=SUM(COUNTIFS($C$5:$C$12,{"Complete","In Progress"}))
And press enter to see the result as 5 which is nothing but the sum of above two results.
Got it friends.
So friends, in this way One can Use Excel COUNTIFS With Multiple Criteria And OR Logic
I hope you have enjoyed this tutorial, If yes, please do give me a like, share and comment.
For further more interesting videos, please do subscribe dptutorials.
For Personalized detail learning, write to dptutorials15@gmail.com
If you found this video valuable, give it a like. If you know someone who needs to see it, share it. Leave a comment below with your thoughts. Add it to a playlist if you want to watch it later.
***********************************************
★ My Online Tutorials ► www.dptutorials.com
LEARNING RESOURCES I Recommend: www.dptutorials.com/resources
Subscribe for more Awesome Tutorials: goo.gl/NyAtg2
Support the Channel via shopping: ift.tt/2jH38PR
Tools that I use for Vlogging:
• Laptop: fkrt.it/AM9ab_uuuN
• Canon 200D Camera: fkrt.it/AMz75_uuuN
• Benro Tripod: fkrt.it/An1lm_uuuN
• Microphone: fkrt.it/A2RHz_uuuN
• Collar Microphone: fkrt.it/yL8kdQNNNN
• Screen recorder: techsmith.pxf.io/2BMjA
• Boom Arm Stand: fkrt.it/ypUD8QNNNN
• Zoom H1 Audio Recorder: fkrt.it/ypb7WQNNNN
• Harison Softbox Studio Lights: fkrt.it/ypCaRQNNNN
• Chroma Key Green Screen: fkrt.it/A3gnb_uuuN
• Background Support Stand: fkrt.it/A3cCb_uuuN
• Acoustic Foam Background: fkrt.it/ypsGdQNNNN
• USB RGB LED Strip: fkrt.it/A3T_5_uuuN
• Wireless Mouse: fkrt.it/A3Bqz_uuuN
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
***********************************************
You Can Connect with Me at:
RUclips: / dptutorials
G+: ift.tt/2kAOpa6
Twitter: / dptutorials15
Facebook: ift.tt/2kfRnDi
BlogSpot: ift.tt/2kB14dh
Follow:
www.dptutorials.com
www.askplanner.blogspot.com
#dptutorials #exceltricks #excellearning #exceltutorials #exceltraining #exceltips
Tags: -
excel formulas in English, excel in English, excel tutorial in English,ms excel in English,ms excel tutorial in English, learn excel in English,vlookup in excel in English, learn ms excel in English, excel training, excel tutorial, Microsoft Excel 2007, learn excel,tutorial excel, ms excel tutorial, excel tutorials,ms excel 2007,Microsoft Excel training,learn excel online,learning excel,free excel training,online excel training,advanced excel tutorial,excel tutorial, excel formulas and functions, excel formulas, excel tutorial in Hindi, excel formulas and functions in Hindi, excel tricks, excel in Hindi, excel shortcut keys, excel vlookup, excel formulas in Hindi, excel for beginners, excel for accounting, excel formulas and functions tutorial, Excel Sum Formula, Sum Formula series, excel attendance sheet, excel salary sheet, excel stock maintain, excel data entry, advanced excel tutorial,excel formulas,excel tutorial,vlookup excel,excel accounting,excel for beginners,excel shortcut keys,excel sum formula,excel training,excel training online,excel tricks,free excel training,learn excel,learn excel online,microsoft excel training,attendance sheet in excel,excel data entry,excel formulas and functions,microsoft excel 2007, ms excel tutorial, excel formulas, and functions in hindi, excel formulas and functions tutorial, excel formulas in english, excel formulas in
The combination of SUM and COUNTIFS really saves me a lot of time and energy. I appreciate it.
The perfect explanation in a short time. I didn't know to add the SUM function. Thank you.
Most welcome
I was stuck forever trying to figure this out thank you
You're most welcome
Really Helpful and Time Saving
Excellent tutorial. Concise with clear explanation. Two thumbs up.
Glad you enjoyed it!
I was looking for this to count samples with different status (A,P,C,U,I,X) and different arrival months and I was not satisfied with adding 5 countifs formula. Thank you for that explanation
Thanks a lot
I was looking for this exact thing. Thank you for explaining so well.
Most welcome
Most welcome
Thank you sir.
Very informative video.
Welcome
I love you thank you you're making my team so much money from the great google sheets tracking
thank you very mutch
Thank you very much sir 😊😊😊
So nice of you
Nice bro 😎 this really helps me
Glad to hear that
Thank alot
Well done
thanks a lot
Wonderful
Thank you! Cheers!
hi i need or condition in Sum ifs formula
is that possible pls explain
Hello. If I needed to use the column "State" as a criteria as well, how would it look like?
Hi, just add the 2nd criteria as usual
Thanks
Welcome
Nice topic friends thanks for sharing. Please make video for updating schedule every week that would be great. Thanks in advance
Thanks a lot for the feedback.
I welcome you to subscribe to this channel to enjoy more interesting videos.
@@dptutorials i already subscribe my friend
If need count other than completed and have lot of conditions like planning, pendinig,inprogress, like more than 5. Can you suggest me how can I use the formula. Finally the result should be total of all the conditions expect completed.
Yes, like explained in this video, you can give multiple conditions using the { ,""}.
What if I wanted to use criteria as a reference of the cell rather than "Completed" or "Pending"? I mean cell F5, F6 & F7. Further, If you further explain, how to use criteria as a reference of the cell in a table.
Sir, suppose we have two column and then we want more than one criteria in each column than what formula we should use....
Hi, You can use the same logic like mentioned in the video.
How can I count absent or leave of a employee that he plan suddenly after and before his rest day
Please mail me the excel sheet. So that I can help you with a solution.
Can you please make a similar video on Google sheets
Surely will try.
Can you please let me know how will it work in Google sheets, little urgent
This is not working on google sheets
Or logic formula is not working in google sheet. if i'm looking for sum of 2 result this formula is working in google sheet please help
Let me explore please
please provide the same in spreadsheet
Sure, will add the link in the description.
nice. please share the file
Surely, i am adding the link in the description for your reference please.
Will this work in google sheets also?
Not exactly
"Complete or in progress" formula doesn't work in Google sheets. Please help
Yes, will check for some solution.
Sir y r u use not very easy function SUMPRODUCT
SUMPRODUCT is always best 😊