Formula with Frequency to count the duplicate values as one

H

Herminne

New Member
Joined
Apr 27, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
Hi there, maybe an easy question for somebody, but increasingly difficult for me:

how can I modify the existing formula to count the duplicate values (invoice numbers in this case) as one? I read on this forum that Frequency formula may be helpful in my case, but couldn't figure it out how to integrate it with existing formula..

The formula is the following:

=COUNTIFS(Overdue_inv_level!A:A,A3, Overdue_inv_level!C:C, "<" &WORKDAY(EOMONTH(TODAY(), -1), 1))-COUNTIFS(Overdue_inv_level_beg_balance!A:A,A3, Overdue_inv_level_beg_balance!P:P, ">0")

Overdue_inv_level and overdue_inv_beg_balance contain the same type of data in their respective columns:
Column A: name and last name
Column B: Document number (invoice number)
Column C: Document Date

Thanks very much in advance for any helpful advice on this.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
I think that this should do what you need, it must be array confirmed with Ctrl Shift Enter, not just Enter.

Note that I have set the formula to sum rows 3 to 200, you will need to adjust this to a realistic number of rows based on your actual data (not entire columns as you had before).

=SUM(IFERROR(1/COUNTIFS(Overdue_inv_level!B3:B200,Overdue_inv_level!B3:B200&"",Overdue_inv_level!A3:A200,A3, Overdue_inv_level!C3:C200, "<" &WORKDAY(EOMONTH(TODAY(), -1), 1)),0))-SUM(IFERROR(1/COUNTIFS(Overdue_inv_level_beg_balance!B3:B200,Overdue_inv_level_beg_balance!B3:B200&"",Overdue_inv_level_beg_balance!A3:A200,A3, Overdue_inv_level_beg_balance!P3:P200, ">0"),0))
 
Upvote 0
Welcome to the forum.

I came up with this formula using FREQUENCY:

=SUM(SIGN(FREQUENCY(IF(Overdue_inv_level!A3:A200=A3,IF(Overdue_inv_level!C3:C200<WORKDAY(EOMONTH(TODAY(),-1),1),MATCH(Overdue_inv_level!B3:B200,Overdue_inv_level!B3:B200,0))),ROW(Overdue_inv_level!B3:B200)-ROW(Overdue_inv_level!B3)+1)))-SUM(SIGN(FREQUENCY(IF(Overdue_inv_level_beg_balance!A3:A200=A3,IF(Overdue_inv_level_beg_balance!C3:C200>0,MATCH(Overdue_inv_level_beg_balance!B3:B200,Overdue_inv_level_beg_balance!B3:B200,0))),ROW(Overdue_inv_level_beg_balance!B3:B200)-ROW(Overdue_inv_level_beg_balance!B3)+1)))

This also requires Control+Shift+Enter, and also requires a fixed row and not whole-column references.

Jasonb75, with the following test sheets and your formula, and A3 = a, I get a result of 4.5 which doesn't seem quite right!

Book1
ABC
1NameInvoiceDate
2a11-Feb
3a11-Feb
4a21-Feb
5a21-Feb
6b11-Feb
7b21-Feb
8c31-Feb
9c31-Feb
10c31-Feb
11c31-Feb
12a41-Feb
Overdue_inv_level


Book1
ABC
1NameInvoiceDate
2a11-Feb
3a11-Feb
4a21-Feb
5a21-Feb
6b11-Feb
7b21-Feb
8c31-Feb
9c31-Feb
10c31-Feb
11c31-Feb
Overdue_inv_level_beg_balance
 
Upvote 0
It does work with COUNTIFS, Eric. I think I've missed an array (or 2) in the formula, your frequency method is most likely more stable.
 
Upvote 0
jasonb75 said:
I think that this should do what you need, it must be array confirmed with Ctrl Shift Enter, not just Enter.

Note that I have set the formula to sum rows 3 to 200, you will need to adjust this to a realistic number of rows based on your actual data (not entire columns as you had before).

=SUM(IFERROR(1/COUNTIFS(Overdue_inv_level!B3:B200,Overdue_inv_level!B3:B200&"",Overdue_inv_level!A3:A200,A3, Overdue_inv_level!C3:C200, "<" &WORKDAY(EOMONTH(TODAY(), -1), 1)),0))-SUM(IFERROR(1/COUNTIFS(Overdue_inv_level_beg_balance!B3:B200,Overdue_inv_level_beg_balance!B3:B200&"",Overdue_inv_level_beg_balance!A3:A200,A3, Overdue_inv_level_beg_balance!P3:P200, ">0"),0))
Click to expand...

Thank you sir, you saved my day! I couldn't have come up to this by myself.
May I also ask you what this part &"" is doing?
 
Upvote 0
Without the &"" part empty cells can be counted incorrectly.

As Eric W has pointed out, there are some issues with the formula, I didn't test it before posting and it would appear that I might have missed part of it. The method that Eric has suggested should be more accurate.
 
Upvote 0
Eric W said:
Welcome to the forum.

I came up with this formula using FREQUENCY:

=SUM(SIGN(FREQUENCY(IF(Overdue_inv_level!A3:A200=A3,IF(Overdue_inv_level!C3:C200<WORKDAY(EOMONTH(TODAY(),-1),1),MATCH(Overdue_inv_level!B3:B200,Overdue_inv_level!B3:B200,0))),ROW(Overdue_inv_level!B3:B200)-ROW(Overdue_inv_level!B3)+1)))-SUM(SIGN(FREQUENCY(IF(Overdue_inv_level_beg_balance!A3:A200=A3,IF(Overdue_inv_level_beg_balance!C3:C200>0,MATCH(Overdue_inv_level_beg_balance!B3:B200,Overdue_inv_level_beg_balance!B3:B200,0))),ROW(Overdue_inv_level_beg_balance!B3:B200)-ROW(Overdue_inv_level_beg_balance!B3)+1)))

This also requires Control+Shift+Enter, and also requires a fixed row and not whole-column references.

Jasonb75, with the following test sheets and your formula, and A3 = a, I get a result of 4.5 which doesn't seem quite right!

Book1
ABC
1NameInvoiceDate
2a11-Feb
3a11-Feb
4a21-Feb
5a21-Feb
6b11-Feb
7b21-Feb
8c31-Feb
9c31-Feb
10c31-Feb
11c31-Feb
12a41-Feb
Overdue_inv_level


Book1
ABC
1NameInvoiceDate
2a11-Feb
3a11-Feb
4a21-Feb
5a21-Feb
6b11-Feb
7b21-Feb
8c31-Feb
9c31-Feb
10c31-Feb
11c31-Feb
Overdue_inv_level_beg_balance
Click to expand...

Thank you very much for your reply, this formula is the work of art! I guess I can add another condition (from my original post) in the the second part of the formula, like IF(Overdue_inv_level_beg_balance!P3:P300, ">0" )
 
Upvote 0
Work of art? Well, maybe abstractionist art!

=SUM(SIGN(FREQUENCY(IF(Overdue_inv_level!A3:A200=A3,IF(Overdue_inv_level!C3:C200<WORKDAY(EOMONTH(TODAY(),-1),1),MATCH(Overdue_inv_level!B3:B200,Overdue_inv_level!B3:B200,0))),ROW(Overdue_inv_level!B3:B200)-ROW(Overdue_inv_level!B3)+1))) -

SUM(SIGN(FREQUENCY(IF(Overdue_inv_level_beg_balance!A3:A200=A3,IF(Overdue_inv_level_beg_balance!C3:C200>0,IF(Overdue_inv_level_beg_balance!P3:P200>0,MATCH(Overdue_inv_level_beg_balance!B3:B200,Overdue_inv_level_beg_balance!B3:B200,0)))),ROW(Overdue_inv_level_beg_balance!B3:B200)-ROW(Overdue_inv_level_beg_balance!B3)+1)))

Sure, adding another condition is easy enough, but you'd add it using standard IF conditions, not SUMIF constructions. You'd also need to add another close parenthesis.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Adding Another Condition to 'Countifs' Formula to not count duplicates
Replies
3
Views
246
Fluff
Fluff
I
  • Question
Formula to count duplicate Values every x # of rows
Replies
8
Views
259
Fluff
Fluff
O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
402
KRice
K
R
  • Question
Applying a formula to a subset of rows
Replies
3
Views
121
rodqntr
R
L
  • Solved
Formula in the helper column. need duplicate to be count as One
Replies
7
Views
147
KRice
K

Forum statistics

Threads
1,214,548
Messages
6,120,146
Members
448,948
Latest member
spamiki

Share this page

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
Back
Top