Solved: Count an array of data based on Sum

[JimS]

I need to create a formula that will count the “unique records” in a list of names (in Column B) – the count should be based on the sum of the value in another column (column C) for the unique records. I would like to count them based on their values being over 3 different values of their totals (ie: >250k, >500k, and > 750k).

Each row is a line item and each account could have several rows that need to be added together in order to determine the total value for the individual account.

In the attachment is an example.

I do not want to use a Pivot Table.

Thanks…

JimS

[Bob Phillips]

Try these array formula

=SUM(--(FREQUENCY(IF(SUMIF($B$2:$B$100,$B$2:$B$100,$C$2:$C$100)>250000,MATCH($B$2: $B$100,$B$2:$B$100,0)),ROW(INDIRECT("1:"&ROWS($B$2:$B$100))))>0))

=SUM(--(FREQUENCY(IF(SUMIF($B$2:$B$100,$B$2:$B$100,$C$2:$C$100)>500000,MATCH($B$2: $B$100,$B$2:$B$100,0)),ROW(INDIRECT("1:"&ROWS($B$2:$B$100))))>0))

=SUM(--(FREQUENCY(IF(SUMIF($B$2:$B$100,$B$2:$B$100,$C$2:$C$100)>750000,MATCH($B$2: $B$100,$B$2:$B$100,0)),ROW(INDIRECT("1:"&ROWS($B$2:$B$100))))>0))

[JimS]

XLD,

Thank you for much...

JimS

[Bob Phillips]

Just for fun, I adapted it so that one formula works in all 3 cases, picking up the value from the text

=SUM(--(FREQUENCY(IF(SUMIF($B$2:$B$100,$B$2:$B$100,$C$2:$C$100)>MID(E6,FIND("$",E6 )+1,FIND("K",E6)-FIND("$",E6)-1)*1000,MATCH($B$2:$B$100,$B$2:$B$100,0)),ROW(INDIRECT("1:"&ROWS($B$2:$B$10 0))))>0))

[JimS]

What if I need to do a Sum instead of a count?

[Bob Phillips]

What are you summing in that case? In other words, where does the unique part come in to summing?

[JimS]

Column C

[Bob Phillips]

No, you are not getting my point. The count was count a value of >250K for unique accounts. If you are summing, where does unique come into it, and where does 250K come into it.

Unless you mean like this

=SUM(IF(SUMIF($B$2:$B$100,$B$2:$B$100,$C$2:$C$100)>MID(E6,FIND("$",E6 )+1,FIND("K",E6)-FIND("$",E6)-1)*1000,C2:C100))

[JimS]

Total all the values only if the Sum of the individual rows for an order (an order is made up of multiple rows) is >250k.

For example Column A are order numbers, there can be several rows with the same order number. Column B are the individual cost per item.

I would like to total all the orders (Column B) in the list only if the Sum for an individual order is >250k.

[Bob Phillips]

Did you try my formula?

[JimS]

Can't make it work. How does it determine if the individual order (same number in Column A) is >$250,000? And then how does it added all the orders together if at the individual order level are >$250K?

[Bob Phillips]

Then you need to explain exactly what you want, because I clearly do not understand.

[Aussiebear]

Bumping thread to reinvigorate

[JimS]

I've attached an example of what I'm looking for.

See the Pivot Table.

I need a formula that will produce the resulting value that is in cell D15, but without using a Pivot Table to sum the individual orders.

Also, sorry for any confusion...

JimS

[Bob Phillips]

If you had bothered to explain yourself, and if you had bothered to look at my formula, you could have seen that a simple adjustment in the light of your modified requirements would have given you what you want

=SUM(IF(SUMIF($A$2:$A$100,$A$2:$A$100,$C$2:$C$100)>MID(E6,FIND("$",E6 )+1,FIND("K",E6)-FIND("$",E6)-1)*1000,C2:C100))

[JimS]

I apologize – I was truly struggling with how to explain what I need. Forgive me...

I did look at your formula and cannot make it work. How is E6 used in the formula.



JimS

[Bob Phillips]

E6 is the cell E6 in your original workbook, that had some text with a threshold value ($250K) in it. E7 had the next value and so on.

[JimS]

Got it. Works perfectly (but you already knew that...)



Thank you for all your help, again sorry for being a pain on this one...