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
 

joshuapaul

New Member
Joined
May 25, 2020
Messages
9
Office Version
  1. 2016
ha!

my bad Jason



namedatehoursevent
john
3/10/2019​
2​
clean
john
3/11/2019​
1​
clean
john
3/11/2019​
1​
scrub
john
2/4/2019​
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
I'm not seeing how you get the results that you say, I would agree with Fluff's results (see below, source of results highlighted).

Not sure why you're getting #VALUE! there is nothing in Fluff's formula that would cause such an error unless you made an incorrect edit.
Book2
ABCDEFGH
1namedatehourseventStart01/01/2019
2john10/03/20192cleanEnd31/12/2019
3john11/03/20191cleanNameHours
4john11/03/20191scrubJohn16
5john04/02/20193scrubDave8
6john04/02/20193cleanMark20
7john10/01/20196cook
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
Sheet1
Cell Formulas
RangeFormula
H4:H6H4=SUM(IFERROR(Table1[hours]/COUNTIFS(Table1[hours],Table1[hours],Table1[name],Table1[name],Table1[name],F4,Table1[date],Table1[date],Table1[date],">="&$G$1,Table1[date],"<="&$G$2),0))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

joshuapaul

New Member
Joined
May 25, 2020
Messages
9
Office Version
  1. 2016
ha! My bad Jason


namedatehoursevent
john
3/10/2019​
2​
clean
john
3/11/2019​
1​
clean
john
3/11/2019​
1​
scrub
john
2/4/2019​
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
 

joshuapaul

New Member
Joined
May 25, 2020
Messages
9
Office Version
  1. 2016
yes fluffs math is correct.
sorry I had two different "example" pages pulled up in front of me.

so I was just now able to pull some of the work hours data from a different report on our program however the only metric left that I would need is the number of work hours performed in the last 30 days,
so is there part of this formula I can replace with just a Today()-30 and not have to have the boxes with the dates in them?

thanks again guys

josh.
 

Fluff

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

ADVERTISEMENT

Considering all those dates are last year or before, you don't need a formula. Just enter 0 into the cell ;)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
For anything within the last 30 days, use
=SUMPRODUCT($C$1:$C$10000,($A$1:$A$10000=F5)*($B$1:$B$10000>=TODAY()-30)*($B$1:$B$10000<>$B$2:$B$10001))
 

joshuapaul

New Member
Joined
May 25, 2020
Messages
9
Office Version
  1. 2016
For anything within the last 30 days, use
=SUMPRODUCT($C$1:$C$10000,($A$1:$A$10000=F5)*($B$1:$B$10000>=TODAY()-30)*($B$1:$B$10000<>$B$2:$B$10001))

I must not have entered it correctly the first time, or maybe it didn't like referencing the dates I had in the boxes.
I switched it to today()-30 and it works perfectly!!

thank you so much for your help,

I owe you a beer.

josh.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,755
Messages
5,626,671
Members
416,200
Latest member
Pulsar3000

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