How do I COUNTIFS with only certain header criteria and also by date??

LaurenHancy

LaurenHancy

Board Regular
Joined
Aug 5, 2010
Messages
179
Hi All,

I need your expertise.

I need to count and sum a range of data which only is referenced to the column name. I need to create a timesheet and I want to create an automatic invoice with a few drop downs. This part I think i can do on my own i just need help with the formulas.

So just to be clear I want to count (greater than o), sum if the criteria matches a name and a month in a drop down.

Hope you can help me. Thank you x

DateMonthOliverMonicaPipWilliam
03/01/17Jan6.5539
04/01/17Jan6.5537
05/01/17Jan7539
06/01/17Jan6.5636
09/01/17Jan6.5539
10/01/17Jan6.5536
11/01/17Jan6.5539
12/01/17Jan7536
13/01/17Jan6.5539
16/01/17Jan6.5537
17/01/17Jan6.5539
18/01/17Jan6.5536
19/01/17Jan7539
20/01/17Jan6.5536
23/01/17Jan6.5539
24/01/17Jan6.5036
25/01/17Jan6.5039
26/01/17Jan7037
27/01/17Jan6.5039
30/01/17Jan6.5036
31/01/17Jan6.5039
01/02/17Feb6.5039
02/02/17Feb7037
03/02/17Feb6.5039
06/02/17Feb6.5036
07/02/17Feb6.5039
08/02/17Feb6.5036
09/02/17Feb7039
10/02/17Feb6.5036
13/02/17Feb6.5039
14/02/17Feb6.5037
15/02/17Feb6.5539
16/02/17Feb7036
17/02/17Feb6.5039
20/02/17Feb6.5036
21/02/17Feb6.5539
22/02/17Feb6.5036
23/02/17Feb7039

<!--StartFragment--> <colgroup><col width="65" span="6" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Also this is a reduced data sheet i have created, if there is a way to do the formulas with the following spreadsheet that would be great:

Total hours required
Total costs
$ Food Cost£2£2£2£2
$ Per Hour£4£4£4£4
Child's NameOliverMonicaPipWilliam
DayDateMonthDrop OffPick Up = Drop OffPick Up = Drop OffPick Up = Drop OffPick Up =
Mon02/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Tue03/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Wed04/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Thu05/01/17Jan08:3015:30708:3017:008.510:0014:00407:0018:0011
Fri06/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Mon09/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Tue10/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Wed11/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Thu12/01/17Jan08:3015:30708:3017:008.510:0014:00407:0018:0011
Fri13/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Mon16/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Tue17/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Wed18/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Thu19/01/17Jan08:3015:30708:3017:008.510:0014:00407:0018:0011
Fri20/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Mon23/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Tue24/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Wed25/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Thu26/01/17Jan08:3015:30708:3017:008.510:0014:00407:0018:0011
Fri27/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Mon30/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Tue31/01/17Jan09:0015:306.508:3017:008.510:0014:00407:0018:0011
Wed01/02/17Feb09:0015:306.508:3017:008.510:0014:00407:0018:0011
Thu02/02/17Feb08:3015:30708:3017:008.510:0014:00407:0018:0011
Fri03/02/17Feb09:0015:306.508:3017:008.510:0014:00407:0018:0011
Mon06/02/17Feb09:0015:306.508:3017:008.510:0014:00407:0018:0011
Tue07/02/17Feb09:0015:306.508:3017:008.510:0014:00407:0018:0011
Wed08/02/17Feb09:0015:306.508:3017:008.510:0014:00407:0018:0011

<colgroup><col span="3"><col><col span="11"></colgroup><tbody>
</tbody>
 
Upvote 0
Try adapting the following formulas (adjust ranges as necessary, replace the "Jan" and "Oliver" with references to your dropdowns).
Caveat: there must be names in non-merged cells right above the cells with the equal sign ("=").

For counting days: =SUMPRODUCT((C7:C34="Jan")*(D5:O5="Oliver")*(D6:O6="=")*(D7:O34>0))
For counting hours: =SUMPRODUCT((C7:C34="Jan")*(D5:O5="Oliver")*(D6:O6="=")*D7:O34)
 
Upvote 0
Thanks so much for your reply.

Those formulas don't seem to work, would that be because the names are in Merged cells?
 
Upvote 0
What are your "Jan", "Feb", ... in column C -- text strings or Excel dates formatted as "mmm"?
 
Last edited:
Upvote 0
LaurenHancy said:
I have used =TEXT(B10, "DDD") to get the date from column B, i have formatted the cells to Text also.
Click to expand...
The =TEXT(...,"DDD") will return a day-of-week, not a month. I was asking about your column C, where you have "Jan" and "Feb".

Anyways, to make things easier, just copy your data sample from Post #2, paste it into a new worksheet, move the names to cells F5, I5, L5, and O5 (right above the "=" signs), and see the formulas work.
 
Last edited:
Upvote 0
Tetra201 said:
The =TEXT(...,"DDD") will return a day-of-week, not a month. I was asking about your column C, where you have "Jan" and "Feb".

Anyways, to make things easier, just copy your data sample from Post #2, paste it into a new worksheet, move the names to cells F5, I5, L5, and O5 (right above the "=" signs), and see the formulas work.
Click to expand...

Hi Yes sorry I copied the wrong data, it is =TEXT(..."MMM")

The count formula still doesn't work i am afraid. The sum of hours works but not the count of days. Would a countifs be better? I Just need to count the number of rows that had data in the hours and month.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Copy & Paste Values onto itself based on Criteria
Replies
4
Views
235
mst3kr
M
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
154
dilshod_k
D
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
1K
phiero21
P
V
  • Question
Date function
Replies
6
Views
285
Joe4
Joe4
B
  • Question
countifs and remove data completed
2
Replies
10
Views
384
MuchToLearn
M

Forum statistics

Threads
1,214,952
Messages
6,122,458
Members
449,085
Latest member
ExcelError

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top