Hi all,
I think it's quite an easy formula but for some reason my countifs are not working.
I am trying to build simple schedule for countifs where all deliveries are counted in totals and AM and PM deliveries seperatley.
Any deliveries which are collected for example today on 28.10.2023 and delivered on 30.10.2023 are to be summed on different tab which dates for 30.10.2023 etc. Deliveries are also need to be summed as AM and PM.
See attached, can anyone please help.
Which version of Excel, please? Your forum profile needs updating.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Total: =COUNTIF($C$4:$C$14,H4)
AM: =COUNTIFS($C$4:$C$14,H4,$E$4:$E$14,"<="&0.5)
PM: =COUNTIFS($C$4:$C$14,H4,$E$4:$E$14,">"&0.5)
To include the date:
AliGW on MS365 Beta Channel (Windows 11) 64 bit
Sheet: 28.10.2023
I will use sharepoint for this spreadsheet.Originally Posted by AliGW
In terms of date will this formula add dates on the tabs ahead?
You still need to update your profile to the version that you are using on Sharepoint - your Windows version is of no use whatsoever. Please update your profile NOW.
No. You already have dates in the other sheets - the formula will reference the cell containing the dates.In terms of date will this formula add dates on the tabs ahead?
If you are expecting sheets to be generated automatically, then you'll need VBA - please clarify.
Hi .
Can i ask what "&0.5" means and how it works ? "&" should conect two values into one .
But dont understand how value 0.5 works , does it split time into half in 12Hour format ?
On provided example cant get the PM values to show .Attachment 847643
@Benny77: Times in Excel are expressed as fractions of a 24 hour period. So, 0.5 is the fraction that represent 12:00 midday.
Hence "<="&0.5 represents times at or before midday (AM) and ">"&0.5 represents times after midday (PM).
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Makes sense .
Still Why does PM doesnt show, what i am doing wrong ? Please see picture atched Attachment 847647
There are NO PM values for that date.
Oh sorry . Have overllooked that the time would be sorted in compliance with the Date .
Again learned something new . Thanks for the answers .
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Its not my thread .
I am newbie in excel so i used worksheet from this thread for learning purposes and asked questions .
I hope i didnt violate any forum rules by doing this ?
Actually, you did (but luckily for you, I didn't notice).
Administrative Note:
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #1 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Well it wasnt a help request but a simple question. Simple questions and answers might be helpful for other newbies that are going to read this or other threads.
As for my questions,- if the moderator says its not alowed i wont do it again .
I supose if my questions arent related to the thread and are no help of any sort to anyone , than they can be removed .
Regards.
@Benny77: thanks for the rep
Strictly speaking, I guess your post could/should be considered as a hijack. That said, I think the request for clarification was a useful question to ask and have answered in the thread.
For future reference, if you are using a thread as part of your learning, you should start a new thread. You can use the sample workbook as the basis for any questions and refer to the original thread for background information. In that way, there is no confusion about question(s) and answer(s), and how they relate to one another.
@Benny77
Post #7 on its own would have been OK, but you then went on to question why things were not working for YOU, and this is the point at which you hijacked the thread. Thank you for not doing this again.
Hi AliGW,
Thank you for your help!
Yes, I expect sheets to be updated automatically ahead. For example if on tab 28.10.2023 I have 2 deliveries for 30.10.2023 at AT I would like this data to be counted in the tab for 30.10.2023 so I can see all totals counting from all tabs at the same time.
What is VBA and how can I get it done?
Question - I want to expand my table to lets say C4 - C50000. And ff I want the formula to look at all cellls in Column C e.g. (C:C) and D and E should I just exclude number from the formula you have provided.
That's not what I meant - are you expecting these dated sheets to be CREATED automatically?
I don't understand WHY you want a separate sheet for each date when you could just filter the data on one tab and add subtotals to count the remaining visible cells.
I can create dated sheets manually myself thats not a problem.
The reason why I want separate data sheets for each date is that I want to build large data sheet with around 5000 deliveries per day so I can look ahead for 3ach day and see overview of deliveries counted for am and pm.
Last edited by AliGW; 10-28-2023 at 11:13 AM. Reason: Please do NOT quote unnecessarily! Use the QUICK REPLY button instead.
You could do that by filtering and subtotalling the master table.
So, do you want to automate the CREATION of the date sheets or not?
Earlier in the thread I asked you to update your forum profile with your Excel version: please don't ignore such instructions. Please do it NOW.
I have updated my forum profile my excel version is Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20806) 64-bit.
I would like it to be manual, I can create new date sheets myself manually by copying previous dates sheets and updating dates and data as long as all formulas are carried forward.
So all you need to do, then, is adapt the formula for each sheet.
The ranges will need to refer to the master sheet, e.g.:
=COUNTIFS(MasterSheet!$C$4:$C$14,H4,MasterSheet!$D$4:$D$14,$B$1)
Apologies I am not great in excel at all.
What will be the formula if I want to include all cells from column C and D + Column E for AM AND PM times and will formula update numbers on next sheet?
See picture I would like highlighted deliveries from 28.10.2023 to be automatically counted on next date tabs.
Try this:
=COUNTIFS(MasterSheet!$C$4:$C$14,H4,MasterSheet!$D$4:$D$14,">="&$B$1)
You must TRY to implement what I have shown you. When you have, if it isn't working as you want, share another copy of the workbook.
Thank you I will try and will let you know if I need anymore help!
Great - happy to help as long as you've had a go.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks