Need forumla for sum of duplicate occurance

[sanjyou]

Need formula to "sum number of duplicate occurance between two dates with one criteria ".

 

[Aladin Akyurek]

Care to post a small sample along with the desired result for that sample?

 

[sanjyou]

Need formula to "sum number of duplicate occurance between two dates with one criteria ".

Thankyou for replying my post .Here i have Post the link for example.


https://docs.google.com/spreadsheets/d/1dPdLIsATDNIzuThgJVhqgdjZhbuAPc-TFnPOtyv_y-Q/edit?usp=sharing

 

[sanjyou]

Thankyou for replying my post .Here i have Post the link for example.


https://docs.google.com/spreadsheets/d/1dPdLIsATDNIzuThgJVhqgdjZhbuAPc-TFnPOtyv_y-Q/edit?usp=sharing

eagarly waiting for result,thanks in advance.

 

[Indystick]

I setup the following test table:

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/1/2018[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/31/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3/2/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5/31/2018[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Criterion[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Count[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

In cell C10 I have the following array formula (requiring the CTRL+SHIFT+ENTER key stroke):

{ =SUM(IF(D2:D7=D9,1,0)) }

As you can see, it correctly sums the number of duplicate values.

Now, this only works if your data is sorted by date and you're going to select the ranges to calculate. If you want to specify the date ranges and the criterion in separate cells and total the dupes, then here is the approach I took (others might have a more elegant solution):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/1/18[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/31/18[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3/2/18[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4/1/18[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5/1/18[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5/31/18[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Start Date[/TD]
[TD]1/1/18[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]End Date[/TD]
[TD]4/1/18[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Criterion[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Count[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

The formula in cell B12 is:

{ =SUMPRODUCT(IF(A1:A6>=B11,1,0),IF(A1:A6<=B12,1,0),IF(B1:B6=B8,1,0)) }

Again, this is an array formula so it requires the CSE keystroke. Also, this is a lot more intuitive if you use range names, E.g., :

 {=SUMPRODUCT(IF(Date>=StartDate,1,0),IF(Date<=EndDate,1,0),IF(CriterionColumn=Criterion,1,0)) }

 

[Indystick]

Here is a more elegant version of the second formula I posted:

 { =SUMPRODUCT(--(A1:A6>=B11),--(A1:A6<=B12),--(B1:B6=B8)) }

 

[Aladin Akyurek]

[1]

{ =SUM(IF(D2:D7=D9,1,0)) }

>>

=COUNTIFS(D2:D7,D9)


[2]

{ =SUMPRODUCT(IF(A1:A6>=B11,1,0),IF(A1:A6<=B12,1,0),IF(B1:B6=B8,1,0)) }

>>

=COUNTIFS(A1:A6,">="&B11,A1:A6,"<="&B12,B1:B6,B8)


[3]

{=SUMPRODUCT(IF(Date>=StartDate,1,0),IF(Date<=EndDate,1,0),IF(CriterionColumn=Criterion,1,0)) }

>>

=COUNTIFS(Date,">="&StartDate,Date,"<="&EndDate,CriterionColumn,Criterion)


[4]

{ =SUMPRODUCT(--(A1:A6>=B11),--(A1:A6<=B12),--(B1:B6=B8)) }

>>

See COUNTIFS of [2].