SUMIFS with a unique date range solution

[AlainTV]

Hello,
I need some help with a formula that will return the sum of the "Quantity" column, for a given "Product", from a unique "Purchased Date".
See sample data below.

Product	Purchased Date	Quantity
Tomatoes	15/03/2021	5
Potatoes	28/04/2021	1
Capsicums	29/04/2021	3
Tomatoes	15/03/2021	2
Potatoes	28/04/2021	1
Capsicums	5/05/2021	6
Tomatoes	8/03/2021	1
Potatoes	14/04/2021	4
Capsicums	21/04/2021	1
Tomatoes	15/03/2021	2
Potatoes	28/04/2021	1
Capsicums	21/04/2021	6

I can use the SUMIFS function to return products and the sum of their respective products, and I can also retrieve the unique purchased date with the UNIQUE(date) function. However, I'm struggling to get them to work together.

Any help will be much appreciated.

Regards,
Alain

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D
1	Product	Purchased Date	Quantity
2	Tomatoes	15/03/2021	5	9
3	Potatoes	28/04/2021	1	3
4	Capsicums	29/04/2021	3	3
5	Tomatoes	15/03/2021	2	9
6	Potatoes	28/04/2021	1	3
7	Capsicums	05/05/2021	6	6
8	Tomatoes	08/03/2021	1	1
9	Potatoes	14/04/2021	4	4
10	Capsicums	21/04/2021	1	7
11	Tomatoes	15/03/2021	2	9
12	Potatoes	28/04/2021	1	3
13	Capsicums	21/04/2021	6	7
Lists

Cell Formulas
Range	Formula
D2:D13	D2	=SUMIFS(C:C,A:A,A2,B:B,B2)

 

[AlainTV]

Hello Fluff,
Thank you for your reply.
While your solution seems to meet the criteria, the end result is a bit different from what I need it to do.

I was trying to simply the query, but in reality what I need to do is to sum all the unique rows, for each of the products, back to a single subtotal cell.

The real requirement is for a Gantt Chart in Excel. (see screenshot attached)

I'm dealing with some duplicates, but given the structure of the document, I must retain the way the data is shown. Looking at the image, the Gantt Chart is designed to only enter full days, if you try to breakdown the task into hours, other parts of the Gantt Chart won't work. In essence, there may be 4 different rows (tasks) that can be achieved within the same day, ergo we shouldn't need to add them to the total, we just need to be able to filter by unique dates, and then sum those values.

I hope its clearer with my explanation.

Regards,
Alain

 

[Fluff]

I'm afraid I have no idea what you are trying to do & it seems to be totally unrelated to your original question.

I was trying to simply the query

This is a very bad idea, as you get something that won't work.

 

[AlainTV]

I'm afraid I have no idea what you are trying to do & it seems to be totally unrelated to your original question.

This is a very bad idea, as you get something that won't work.

Hi Fluff,
There is no easy way to look for unique date entries across the given column, so that we only include a single entry on the sum for rows that meet the criteria of "TASK = Commissioning" and "DATE = Unique date" before adding the WORK DAYS value to the total?

Thanks for your time and help

 

[Fluff]

You can use the sumifs function that showed for that.