Summing unique value using daytime as criteria

[Luc_69]

Hi All - I need to know how to sum unique values and use the date as a criteria or filter. For example

Ive been trying something like productsum(1/countif(value_range,value_range),value_range) but thats as far as I get ??

Any ideas ??

 

[DanteAmor]

Hi and welcome to MrExcel!

How about with array formula:

Dante Amor
	A	B	C	D	E	F
1	DATE	VALUE			SOLUTION	FORMULA
2	02-feb	40			02-feb	110
3	02-feb	10			03-feb	210
4	02-feb	10
5	02-feb	10
6	02-feb	60
7	03-feb	90
8	03-feb	40
9	03-feb	20
10	03-feb	20
11	03-feb	60
Hoja10

Cell Formulas
Range	Formula
F2:F3	F2	=SUM(IF(FREQUENCY(IF($A$2:$A$11=E2,MATCH($B$2:$B$11,$B$2:$B$11,0)),ROW($B$2:$B$11)-ROW($B$2)+1),$B$2:$B$11))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Luc_69]

Hi and welcome to MrExcel!

How about with array formula:

Dante Amor
	A	B	C	D	E	F
1	DATE	VALUE			SOLUTION	FORMULA
2	02-feb	40			02-feb	110
3	02-feb	10			03-feb	210
4	02-feb	10
5	02-feb	10
6	02-feb	60
7	03-feb	90
8	03-feb	40
9	03-feb	20
10	03-feb	20
11	03-feb	60
Hoja10

Cell Formulas
Range	Formula
F2:F3	F2	=SUM(IF(FREQUENCY(IF($A$2:$A$11=E2,MATCH($B$2:$B$11,$B$2:$B$11,0)),ROW($B$2:$B$11)-ROW($B$2)+1),$B$2:$B$11))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Thanks for that - it works !

 

[HappierThan]

??? I am puzzled by the mathematics, could someone please tell me why the results aren't F2 = 130 and F3 = 230?

 

[DanteAmor]

I am puzzled by the mathematics, could someone please tell me why the results aren't F2 = 130 and F3 = 230?

@Luc_69 only needs to sum the unique values:

I need to know how to sum unique values and use the date as a criteria

Dante Amor
	A	B
1	DATE	VALUE
2	02-feb	40
3	02-feb	10
4	02-feb	10
5	02-feb	10
6	02-feb	60
Hoja10

In this case the value "10" is repeated, only one value should be considered, then: 40 + 10 + 60 = 110

 

[DanteAmor]

Thanks for that - it works !

I'm glad to help you. Thanks for the feedback.

 

[mole999]

@Luc_69
Your post has been reported as being cross posted with another site.

Whilst we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: > (Message Board Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please provide the link(s) ASAP.

 

[Luc_69]

@Luc_69
Your post has been reported as being cross posted with another site.

Whilst we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: > (Message Board Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please provide the link(s) ASAP.

Sorry about that - I should have read the fine print but was in a hurry to get an answer. Will not do again
Below is the link to another board

How to sum unique value with date as a critria [SOLVED]

Hi All - I need to know how to sum unique values and use the date as a criteria or filter. For example 672971 Ive been trying something like productsum(1/countif(value_range,value_range),value_range) but thats as far as I get ?? Any ideas ??

www.excelforum.com

 

[Luc_69]

I'm glad to help you. Thanks for the feedback.

Hi DanteAmor - with the solution you provided, I have the CSE every cell ( Ive got about 500 cells to do) - is there are way to bulk CSE these cells ??

 

[DanteAmor]

You just have to do CSE in the first formula.
Then you can copy or drag the formula.