Help!- summing a category based on unique distinct criteria AND and second category?

[Legacy 461885]

hey everyone,

firstly i'm really new to Excel so I don't know how to use VBA and I would like to be able to solve this with some sort of basic function or CSE cause that's all I really know how to do. I've run into a problem that I need a solution for, and none of the ones I've found online for similar data have been able to work for me.

This really is only because the program that we use at work presents the data in a really unhelpful format so we'll have to get pretty creative to figure this one out I think.

The data I have is presented in list form with everyone's name in A the date that they worked in B the number of hours they worked in C and a description of the tasks they did in D

In our logbook program, when the employee makes an entry they fill out the date and hours they worked, and then check a box for all the events that they completed, and in the log program itself it only counts the hours they worked once, but when you export the data to Excel, it makes a separate entry for each event they completed with the same date and hours worked for each one.

so i'm running a =sumifs() based on the name criteria and a specific date range and it comes back with inflated work hours because of the duplicate entries.

I need a function for the green box (J8) that will sum all the numbers in C:C only for a specific name that I type in the blue box (H8), and only within the date range that I enter in the date boxes (L4 and L5)

The only distinguishing criteria I have is the date entry, I cannot just sum unique numbers in C:C because they employee may work the same hours on two separate days and I need both of them to be counted. and I cannot sum based on unique criteria in D:D because they may perform the same task on different days. the tasks themselves are irrelevant, I just need the hours counted correctly.

if it makes a difference, the data is on a separate sheet from the cells I want the information displayed in but that should be relatively easy for me to format the code correctly for that I think?

I also need the code to be able to handle the data A:A B:B C:C D:D being entire columns because I don't have anyway of knowing how long the lists will be as this will be covering a year or more's worth of log entries, (probably in the nature of 5xxx-6xxx rows)

also I don't have 2019 excel I'm still on the one before that.

thanks for everyone's help with this,
I'd love to be able to get this to work.

josh.

 

[jasonb75]

That just needs a basic sumifs formula

=SUMIFS(C:C,A:A,H8,B:B,">="&$L$4,B:B,"<="&$L$5)

Note that L4 and L5 will need to contain valid dates. L4 currently contains a text string that looks like a date.
You could use =TODAY() in L5 so that it always contains the current date, or enter any valid date to restrict it to a different period.

 

[Legacy 461885]

That just needs a basic sumifs formula

=SUMIFS(C:C,A:A,H8,B:B,">="&$L$4,B:B,"<="&$L$5)

Note that L4 and L5 will need to contain valid dates. L4 currently contains a text string that looks like a date.
You could use =TODAY() in L5 so that it always contains the current date, or enter any valid date to restrict it to a different period.

Jason,
thanks for the quick response,
yes, I just wrote "today" as a visual example.

the code you provided does sum the work hours between the date range, but it sums ALL the date entries even if they occured on the same day. I need the SUMIFS function to sum ONLY the work hours that occurred on unique dates in order to filter out the duplicate entries.

thanks,

josh.

 

[Legacy 461885]

I currently have this in the box

=SUMIFS($C:$C,$A:$A,$H$8,$B:$B,"<>"&$B4)

and this will let me add the values excluding whatever ones I manual select and put into the function (in this case B4) but I can't do this for the whole work sheet.

I need the function to automatcially check to see if the date value is equal to the date value of the cell above it and if it IS then not include that cell value in the Sumifs. but I don't know how to get the criteria range or criteria to reference the cell above it every time.

hope that helps explain what I need.

josh

 

[jasonb75]

Yep I get what you mean now, my eyes had a freudian slip while I was reading your question.

Could you copy A1:D22 from your sheet and paste it directly to the forum please, saves me retyping it from your screen capture.

 

[Legacy 461885]

name	date	hours	event
john	3/10/2019​	2​	clean
john	3/11/2019​	1​	clean
john	3/11/2019​	1​	scrub
john	2/4/2109​	3​	scrub
john	2/4/2019​	3​	clean
john	1/10/2019​	6​	cook
john	12/25/2019​	4​	clean
dave	3/5/2019​	2​	clean
dave	3/5/2019​	2​	scrub
dave	3/3/2019​	1​	cook
dave	2/20/2019​	3​	wash
dave	2/15/2019​	2​	trash
dave	2/15/2019​	2​	clean
dave	12/29/2018​	1​	scrub
mark	3/3/2019​	1​	wash
mark	3/1/2019​	2​	scrub
mark	2/14/2019​	3​	clean
mark	2/13/2019​	5​	trash
mark	1/10/2019​	4​	clean
mark	12/15/2019​	2​	cook
mark	12/5/2019​	3​	wash

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G
1	name	date	hours	event
2	john	10/03/2019	2	clean		Start	01/01/2019
3	john	11/03/2019	1	clean		End	25/05/2020
4	john	11/03/2019	1	scrub
5	john	04/02/2109	3	scrub		John	16
6	john	04/02/2019	3	clean		Dave	8
7	john	10/01/2019	6	cook		Mark	20
8	john	25/12/2019	4	clean
9	dave	05/03/2019	2	clean
10	dave	05/03/2019	2	scrub
11	dave	03/03/2019	1	cook
12	dave	20/02/2019	3	wash
13	dave	15/02/2019	2	trash
14	dave	15/02/2019	2	clean
15	dave	29/12/2018	1	scrub
16	mark	03/03/2019	1	wash
17	mark	01/03/2019	2	scrub
18	mark	14/02/2019	3	clean
19	mark	13/02/2019	5	trash
20	mark	10/01/2019	4	clean
21	mark	15/12/2019	2	cook
22	mark	05/12/2019	3	wash
Lookup

Cell Formulas
Range	Formula
G3	G3	=TODAY()
G5:G7	G5	=SUMPRODUCT($C$1:$C$10000,($A$1:$A$10000=F5)*($B$1:$B$10000>$G$2)*($B$1:$B$10000<$G$3)*($B$1:$B$10000<>$B$2:$B$10001))

 

[jasonb75]

Fluff's formula is better than the one I came up with, luckily I spent 10 minutes trying to figure out why mine wasn't evaluating as I expected (caused by the year in B5) otherwise I would have posted an embarrassingly inefficient formula

 

[Fluff]

I had problems because everything I initially tried just turned into a spill range.
Just one of the "joys" of the new dynamic arrays.

 

[Legacy 461885]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G
1	name	date	hours	event
2	john	10/03/2019	2	clean		Start	01/01/2019
3	john	11/03/2019	1	clean		End	25/05/2020
4	john	11/03/2019	1	scrub
5	john	04/02/2109	3	scrub		John	16
6	john	04/02/2019	3	clean		Dave	8
7	john	10/01/2019	6	cook		Mark	20
8	john	25/12/2019	4	clean
9	dave	05/03/2019	2	clean
10	dave	05/03/2019	2	scrub
11	dave	03/03/2019	1	cook
12	dave	20/02/2019	3	wash
13	dave	15/02/2019	2	trash
14	dave	15/02/2019	2	clean
15	dave	29/12/2018	1	scrub
16	mark	03/03/2019	1	wash
17	mark	01/03/2019	2	scrub
18	mark	14/02/2019	3	clean
19	mark	13/02/2019	5	trash
20	mark	10/01/2019	4	clean
21	mark	15/12/2019	2	cook
22	mark	05/12/2019	3	wash
Lookup

Cell Formulas
Range	Formula
G3	G3	=TODAY()
G5:G7	G5	=SUMPRODUCT($C$1:$C$10000,($A$1:$A$10000=F5)*($B$1:$B$10000>$G$2)*($B$1:$B$10000<$G$3)*($B$1:$B$10000<>$B$2:$B$10001))

fluff,

that doesn't seem to be working for me in my workbook, i'm getting #value.

The formula should return 12,8,15 as the correct work hours. 16,8,20 are just the total hours INCLUDING the duplicates.