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

joshuapaul

New Member
Joined
May 25, 2020
Messages
9
Office Version
  1. 2016
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.
excel example.png
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,489
Office Version
  1. 365
Platform
  1. Windows
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.
 

joshuapaul

New Member
Joined
May 25, 2020
Messages
9
Office Version
  1. 2016
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.
 

joshuapaul

New Member
Joined
May 25, 2020
Messages
9
Office Version
  1. 2016
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,489
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

joshuapaul

New Member
Joined
May 25, 2020
Messages
9
Office Version
  1. 2016
namedatehoursevent
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,735
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
+Fluff New.xlsm
ABCDEFG
1namedatehoursevent
2john10/03/20192cleanStart01/01/2019
3john11/03/20191cleanEnd25/05/2020
4john11/03/20191scrub
5john04/02/21093scrubJohn16
6john04/02/20193cleanDave8
7john10/01/20196cookMark20
8john25/12/20194clean
9dave05/03/20192clean
10dave05/03/20192scrub
11dave03/03/20191cook
12dave20/02/20193wash
13dave15/02/20192trash
14dave15/02/20192clean
15dave29/12/20181scrub
16mark03/03/20191wash
17mark01/03/20192scrub
18mark14/02/20193clean
19mark13/02/20195trash
20mark10/01/20194clean
21mark15/12/20192cook
22mark05/12/20193wash
Lookup
Cell Formulas
RangeFormula
G3G3=TODAY()
G5:G7G5=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

Well-known Member
Joined
Dec 30, 2008
Messages
12,489
Office Version
  1. 365
Platform
  1. Windows
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 :eek:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,735
Office Version
  1. 365
Platform
  1. Windows
I had problems because everything I initially tried just turned into a spill range. :mad:
Just one of the "joys" of the new dynamic arrays.
 

joshuapaul

New Member
Joined
May 25, 2020
Messages
9
Office Version
  1. 2016
How about
+Fluff New.xlsm
ABCDEFG
1namedatehoursevent
2john10/03/20192cleanStart01/01/2019
3john11/03/20191cleanEnd25/05/2020
4john11/03/20191scrub
5john04/02/21093scrubJohn16
6john04/02/20193cleanDave8
7john10/01/20196cookMark20
8john25/12/20194clean
9dave05/03/20192clean
10dave05/03/20192scrub
11dave03/03/20191cook
12dave20/02/20193wash
13dave15/02/20192trash
14dave15/02/20192clean
15dave29/12/20181scrub
16mark03/03/20191wash
17mark01/03/20192scrub
18mark14/02/20193clean
19mark13/02/20195trash
20mark10/01/20194clean
21mark15/12/20192cook
22mark05/12/20193wash
Lookup
Cell Formulas
RangeFormula
G3G3=TODAY()
G5:G7G5=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,994
Messages
5,628,029
Members
416,287
Latest member
wanji

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
Top