Sum values for different categories in an array within specific dates

0
votes

So, I have a table with several different values that I need to get the totals for a specific user on a determined date range. I was able to get the date range with SUMIF and VLOOKUP but can't add all users with the same name. With SUMIF and INDEX & MATCH I was able to sum all values for a specific user but can't get to select a specific date range now, it sums everything on the column and the idea is to sum without having to dissect the information.

This is an example of the data I have and how I would like to be the output based on the date range. By just changing the dates in the start and end dates fields (cells J1 & J2) the information should change automatically.

enter image description here

2
exceldaterangevlookuplookup
Try SUMIFS or COUNTIFS or use a Pivot Table with slicers or filters – Ron Rosenfeld
you can insert data using a tool like markdown table generator to insert data. When pasting, highlight and press Ctrl +K to ensure inserted properly. Then we can copy the data for testing. Also, include the formulas you have tried as detailed in your question. – QHarr

2 Answers

0
votes

enter image description here

This should be enough to get you started:

 `=SUMPRODUCT(
 (Productivity[User]=$G6)*
 (Productivity[Chats])*
 (Productivity[Date]>=$H$1)*
 (Productivity[Date]<=$H$2))`
0
votes

You should be able to copy and paste this formula into Cell J5 and drag the equations over:

=SUMIFS(INDEX($C$3:$E$17,,MATCH(J$5,$C$2:$E$2,0)),$A$3:$A$17,$I6,$B$3:$B$17,">="&$J$1,$B$3:$B$17,"<="&$J$2)

I was able to duplicate the desired results with the above equations.