COUNTIFS that change
Hello, I am not sure if COUNTIFS is the right formula, this might be something better done with a pivot table. I have a file with dates in column A, the other columns represent individual salespeople and where they worked on a given date. What I need to be able to do is count how many days Jim worked in each city for each given time period, not including Sat & Sun. For example Period 1, Jim worked 6 days in NY, 2 days in SC. Period 2, Jim worked 16 days in OH. Any ideas? thanks!
Attached a sample Excel workbook.
To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.
Do not try to use the Paperclip icon, as this does not work on this forum.
Hope this helps.
Thanks for the VLOOKUP & MATCH and the INDEX and MATCH suggestions. My issue is that the data is daily so I have to be able to count the number of times at a given location by time period and sales person. I also need to be sure the count does not include Saturdays or Sundays. Here is a small sample of what the data loooks like
Date Day of week Month Sue Mike Sam
1/1/2019 Tue Jan CA RI OR
1/2/2019 Wed Jan CA RI OR
1/3/2019 Thu Jan RI OR
1/4/2019 Fri Jan CA
1/5/2019 Sat Jan CA MI
1/6/2019 Sun Jan CA MI NE
1/7/2019 Mon Jan CA MI NE
1/8/2019 Tue Jan CA MI NE
1/9/2019 Wed Jan MI NE
In this case Sue was in CA for 5 days, Mike RI for 3, MI for 3, Sam OR for 3, NE 3
Sue Mike Sam 1/1/2019 Tue jan CA RI OR CA 1/2/2019 Wed jan CA RI OR RI 1/3/2019 Thu jan RI OR OR 1/4/2019 Fri jan CA MI 1/5/2019 Sat jan CA MI NE 1/6/2019 Sun jan CA MI NE 1/7/2019 Mon jan CA MI NE 1/8/2019 Tue jan CA MI NE 1/9/2019 Wed jan MI NE
I2=IF(AND($H2<>"",I$1<>""),SUMPRODUCT(($D$1:$F$1=I$1)*(WEEKDAY($A$2:$A$10,2)<=5)*($D$2:$F$10=$H2)),"")
copy across and down
Wow, that's great!
Any suggestions for counting by Month? I need to have a seperate value for Sue, NY in Jan and Sue, NY in Feb. The same for Mike and Sam.
Date Day of week Month Sue Mike Sam
28-Jan Mon Jan NY MI
29-Jan Tue Jan NY MI TX
30-Jan Wed Jan NY MI TX
31-Jan Thu Jan NY TX
1-Feb Fri Feb NY SC TX
2-Feb Sat Feb NY SC TX
3-Feb Sun Feb NY SC TX
4-Feb Mon Feb NY SC TX
5-Feb Tue Feb NY TX
Can you attach a real excel file ?
sure
This is my first day doing this. I've tried clicking on the icon with the paperclip for attachments, but all I get is a white bar. Is there something else I should be doing?
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
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.
You will simplify this if you capture the data in normalised 2 dimensional table.
In your case create columns for
Date
State
Person
Days
You'll then be able to use a Pivot table to analyse and present your data. You don;t really need separate columns for Day of week or Month since they may be derived from the Date column
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I followed the steps through manage attachments. Please let me know if you received it.thanks!
See attached for a Pivot Table approach
Sue Mike Sam CITY NY Mon jan NY MI OR Tue jan NY MI TX Sue Mike Sam Wed jan NY MI TX jan Thu jan NY TX Feb Fri Feb NY SC TX Sat Feb NY SC TX Sun Feb NY SC TX Mon Feb NY SC TX Tue Feb NY TX
I4=IF(AND($H4<>"",I$3<>""),SUMPRODUCT(($C$2:$C$10=$H4)*($D$2:$F$10=$I$1)*(WEEKDAY($A$2:$A$10,2)<=5)*($D$1:$F$1=I$3)),"")
copy across and down
For each city, changing the code to I1 gives you the corresponding values
Use a pivot table for this work
Sorry for my English
Last edited by CARACALLA; 07-09-2019 at 05:30 PM.
Richard,
Thank you so much for your help. I'm glad I have a working sample of a pivot table with slicers. Unfortunately, I probably need a different approach. As of today, my data set covers 2 years, with over 30 people and over 50 locations. My initial thought is that would be too much to manage in 3 columns. I will think it over on my drive home. Thanks!
Re-arranging data can seem a step too far for large data sets, however it's usually possible to write a simple macro which will do the task automatically and avoid lots of manual copying/pasting. If you want to pursue this, upload a somewhat larger representaive sample and I'll see what can be done.Originally Posted by Vicki3809
Richard,
Thank you so much for your help. I'm glad I have a working sample of a pivot table with slicers. Unfortunately, I probably need a different approach. As of today, my data set covers 2 years, with over 30 people and over 50 locations. My initial thought is that would be too much to manage in 3 columns. I will think it over on my drive home. Thanks!
Caracalla,
Thank you for all of your help.
And your English is fine! In fact very good
Vicki3809
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks