Excel Magic Trick 545: Count Between Dates with 3 Criteria COUNTIFS & DCOUNTA & SUMPRODUCT
ฝัง
- เผยแพร่เมื่อ 14 ต.ค. 2024
- Download Files:
people.highlin...
See how to count records that have a date between the begin and end date and a second column contains the criterion value using these functions:
COUNTIFS
DCOUNTA
SUMPRODUCT
Function formula
Beyond my expectations, I thank you for your teachings
I have been looking EVERYWHERE for a formula to do this!! Thank you
You are welcome, Maria!!!! Thanks for your support of the excelifun channel at TH-cam : ) P.S. how did you eventually find this video?
@@excelisfun Your video comes up on this website ms-office.wonderhowto.com/how-to/count-number-values-between-two-givens-excel-332937/
Thanks for being alive. You saved may sanity 🫂
You are welcome!!!
It is OK to include blanks below your data set and then add data later. For the formulas, you would just expand the ranges. For example, if you wanted to include two rows below row 26 and then add data later, the formulas would look like this:
one as seen in video:
=DCOUNTA(A9:B26,B9,D9:F10)
Formula that includes two blank rows below row 26:
=DCOUNTA(A9:B28,B9,D9:F10)
The other two formulas would similarly get expanded ranges.
Yes, the criteria of "*" should work just fine. You might try "?*" also. "?*" means one or more characters and is technically safer than "*" because "*" will count a null text string, which you probably do not have. "*" works fine if you have no null text strings like a formula blank from an IF-type formula.
Yes. Use sheet references or workbook references. See this video:
Highline Excel Class 06: Worksheet & Workbook References
But use the SUMPRODUCTS method becasue COUNTIFS SUMIFS etc don't do workbook references very well.
@greeshmallya , u r welcome!
For All Your Great Work Let Me Thank You ,Your Way Of Explaining And Teaching Is Really Inspiring and Very Good And Helpful ,You Have Been A Great Help With Many Lessons ... Thank You Again And Keep Up The Good Work
If cells contains A or B but never both, then you can use two COUNTIF or two COUNTIFS functions and put a plus between the two countif functions. If you are using DFunctions, OR Criteria goes on two different lines below the filed name. I have a few videos on OR Criteria. Here is one:
Excel Magic Trick #180: COUNTIF function formula 21 Examples
at the 1:34 minute mark
You could also use the Table Feature to get expandable ranges. See this video:
Excel Dynamic Chart #3: Table Feature (Excel 2010 / 2007) List Feature (Excel 2003)
Sure, just put the text where the A is. Actually, the letter A is a text string with a single character, you can have any text string you want as criteria.
THANK YOU! THANK YOU! THANK YOU! After much frustration attempting to calculate 3 fields, my brain says, "Thank you"!
You are welcome, Hrishikesh!!!
This was INCREDIBLY helpful!!! Thank you so much for this post :-)
Perfect - just what I needed! Thank you for posting this!
Really helpful video. Thank you very much
thank you you helped me today!!
You are welcome!
&!
Thanks a lot. I know countif, countifs function but I did not know how to conbine them. I am looking for a solution as like that. Thanks a lot. Its help me a lot.
Mr. EIF,
Love your show. First time - long time (sports call-in reference just in case .... not as funny when explained).
Could the above "Count between dates" be created on a worksheet pulling from a second worksheet in the same workbook or even different workbooks? I envision a dashboard grabbing from the latest and earlier files.
I regularly import information downloaded database into Excel 2003. I format to create the columns and add a header. There are about 25,000 rows and 35 cols..
Hi your videos are very useful, how do you count data on a another sheet in the work book in a specific date range and a set criteria?
Hi sir,
Your videos are very helpful, i have a problem that i got a data base with name start date and end date and status (p-Present, a-Absent, sl-Sick leave etc, I have amain sheet with dates and employee names, how can I get filled with cell with ststus of a employee in main sheet matching his criteria between dates and status
Awesome 👍
Thanks for this video. This is great! How do we use countif and frequency formula to select unique values if the duplicate rows are more than one? The columns are dates and time values. Please help
Thanks U brother it's very helpful for me 😊
You are welcome, Arman!!! I am glad that it is helpful for you!
Thank you, very helpful
You are welcome, Ciprian!!!
Thanks Mike, I always forget the & when using comparative operators... :)
Thank you for your reply. the text varies on each cell. I am trying to count the number of tasks per week and the text is the description of the task. I am using "*" instead of the A criteria at the end of the formula
Thankyou sir🙏🙏
What if you don't have a separate table establishing the date range and criteria? For example, I have a column containing specific dates that fall into years ranging from 2011 to 2019. The scenario, I need to count how many certificates were issued for a particular year and is either a basic certificate or advanced certificate (separate column). Your example is quite similar to mine, however, I do not have a separate table for the date range begin, end and criteria. I've tried the COUNTIFS and SUMPRODUCT functions but had no luck. Any suggestions or clarifications? Thank you so much. The video has helped a great deal in understanding the functions and how they work.
=SUMPRODUCT(--($D$2:$D$78>=1/1/2015),--($D$2:$D$78
HEY BRO!!! ONCE I GET THIS JOB I APPLY AND NEED TO SUBMIT A EXCEL FILE - I PROMISE I'LL SEND MONEY TO BUY A COFFEE!!
LOVE x 1000
I understand how to do this, great video, but how would I alter the function to count the range contains "A" or "B"?
If there is a video on this I'd happily take a link to that over a comment response if you like.
Will do. Thank you.
Yes, TH-cam is no good for having dialog to get Excel solutions. Any way, try:
"*?", not "?"
Try question mark and asterisk, not just question mark.
Good morning sir,
Sir, your videos are useful for the learners.
There is one issue with countifs. when I try to get the count for more than 3 criteria and with large amount of data(more than 10000 rows) I am getting an error or zero and sometimes wrong figure in answer.
Please assist
Can you expand the search criteria so that it includes cells that are currently blank but will be filled later? I'm using a table and tabs like video 538 and some of the rows will be blank but I still need it to look at these cells because they will be filled with data later. or is there a better way I should do this? thanks
Hello sir, i have implemented "Countif" formula to extract values from different sheets within defined date range but it didn't work. Can i share sample file with you through which you can guide me?
Hi i am facing the problem with countif function with combination of the date function..i tried to figure out the satuday and sundays in a month with the help of countif but im not getting the result..will u please help me out is there any other formula for that ..
Hi I don't know if this is the right video or not but I'm going to submit my question here
I have 300 data in dates ranging from 2016 do 2004, and I want co count how many of this dates is from 1.1.2016 to 31.12. 2016 (one year), and ten for each year I want t the count how can I do this .
Thanks
You are welcome!
Hello bother, i would like you show me how to count multiple product between dates, it mean we just put the date 01/05/2019-30/06/2019 in excel it show each product are count correctly. Thank you in advance
Dear sir,
What if I have single date criteria of counting of attendance .
One more thing sir is countifs is applicable for data in horizontal and attendance in vertical .
Kindly help.
how to Calculate the PR to PO duration based on the followings:
If LOA date is available ignore the PO date and calculate the Cycle time from LOA date less RFQ Date (if RFQ date is missing consider the PR Approval Date)
Waiting your reply.
Can this be done the same way with time instead of Date?
Yes, but time is always a number between 0 and 1, where 8 hours = 8/24 = 1/3 or 0.333333333333333
what if instead of having the letter A, B or C on the third criteria I just have text. I am trying to count the number of instances where we have text on rows between two specific dates.
HI. im facing a problem when using more than 3 criteria. lets say, using your example, your 3rd criteria is A. but I want to add the 4th criteria which is 'B'. But it is giving me zero answer. please help
i did -- I wast just describing that I have also used COUNTIF on another instance only to count the number of cells that have text, and it works ok --- I am using COUNTIFS to count the cells that have text between two date periods --- just tried again to post the formula, but got an error from youtube
tried it, still having the same problem. when I use the countif function isolated, just to count the number of instances where there is text in that same column, countif(E9:E54,"*") it works fine, but when I try with the date range it gives me the #VALUE! error
I have a list of thousands of dates in MM/DD/YYYY format. I want to count how many unique values in a specific month. For example: In this list of thousands of dates, how many unique dates were there is the month of September 2019? I can't figure out for the life of me how to make this happen.
In Excel 2010 or later, use formula: =SUMPRODUCT(--(FREQUENCY(IF(Date>=First_Of_Month,IF(Date0))
2013 or later you can use a Dat Model PivotTable and use the Distinct Count Formula
Office 365 use formula: =ROWS(UNIQUE(FILTER(Date,((Date>=First_Of_Month)*(Date
WTB Helbreath, please help support my efforts to provide free education and coaching by commenting and thumbs up on each video that you watch and your Sub : ) I depend of it!!!
You have to use COUNTIFS, not COUNTIF.
Hi dear...thank you for your support....I need more support from you
how can count data with multiple criteria in one range
I tried "?" but I am still getting a #VALUE!
I wish I could send you the formula but when I paste into this message I get an error and cannot post the message
Question: I cannot figure out how to use this with multiple sheets. Example in my workbook, I have 4 sheets. (Invoices) (Customers) (Violation Information) & (Statistics). I am trying to copy this concept to the statistics page, which is blank. All it would show is statistical data. However all of the information is between (Invoices) & (Violation Information) Sheet. How can I adapt this [End Date] / [Begin Date] / [Criteria] with the [Total Number] that would be between The (Violation Information) sheet columns and the (Invoices) sheet columns. I cannot get this to work. How can I reference the other sheets data to provide results? This is something that I have been working on for hours, and I cannot figure it out. Please help!
youtube will does not allow me to paste the formula for some reason
THANK YOU! THANK YOU! THANK YOU! After much frustration attempting to calculate 3 fields, my brain says, "Thank you"!