help with formula with multiple criterias

Clowe

New member
Joined
Jun 5, 2018
Messages
4
Reaction score
0
Points
0
Excel Version(s)
Excel 2016
Hi,

I'm trying to write a formula with multiple criteria.

Scenario:
spreadsheet with multiple columns
A: Doc Type (approx.4 different types)
B: Doc # (multiple numbers, some may repeat as the document may include multiple lines
C: $amount

will like to count the number of transactions where the amount is more than $50K. But if the Doc # appears more than once, it needs to count it as one transaction and sum the total to compare to the $50K.

Am I being too ambitious?

Please help.
Chris
 
Assuming data in A2:C10, and you are only looking at columns B2:B10 for this formula, then try this Array* formula:

=SUM(IF(FREQUENCY(IF(SUMIF(B2:B10,B2:B10,C2:C10)>50,MATCH(B2:B10,B2:B10,0)),ROW(B2:B10)-ROW(B2)+1),1))

If you also want to include the DOC Types in column A for the unique count, then use:

=SUM(IF(FREQUENCY(IF(SUMIFS(C2:C10,A2:A10,A2:A10,B2:B10,B2:B10)>50,MATCH(A2:A10&B2:B10,A2:A10&B2:B10,0)),ROW(B2:B10)-ROW(B2)+1),1))

adjust ranges to suit.

*confirmed with CTRL+SHIFT+ENTER not just ENTER
 
Last edited:
Thank you.
 
alittle problem - I'm entering the formula but my answer is 0 - which is incorrect. Not sure what I am doing wrong.
 

Attachments

  • sample data.xlsx
    10.8 KB · Views: 10
You don't show the formula in your workbook?

I used this formula and got a result of 12.

=SUM(IF(FREQUENCY(IF(SUMIF(B4:B39,B4:B39,C4:C39)>50,MATCH(B4:B39,B4:B39,0)),ROW(B4:B39)-ROW(B4)+1),1))

remember that after you type the formula in, you need to hold the cTRL and SHIFT keys down and hit ENTER as this is an Array formula.
 
thank you - it worked... yayyyyyy!!!
 
Back
Top