PDA

View Full Version : Solved: Count an array of data based on Sum



JimS
07-27-2010, 01:50 PM
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
07-27-2010, 02:52 PM
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
07-28-2010, 05:26 AM
XLD,

Thank you for much...

JimS

Bob Phillips
07-28-2010, 06:03 AM
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$100))))>0))

JimS
07-28-2010, 07:50 AM
What if I need to do a Sum instead of a count?

Bob Phillips
07-28-2010, 08:03 AM
What are you summing in that case? In other words, where does the unique part come in to summing?

JimS
07-28-2010, 08:19 AM
Column C

Bob Phillips
07-28-2010, 08:32 AM
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
07-28-2010, 08:58 AM
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
07-28-2010, 09:58 AM
Did you try my formula?

JimS
07-28-2010, 10:12 AM
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
07-28-2010, 03:10 PM
Then you need to explain exactly what you want, because I clearly do not understand.

Aussiebear
07-29-2010, 01:17 AM
Bumping thread to reinvigorate

JimS
07-29-2010, 07:56 AM
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
07-29-2010, 08:43 AM
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
07-29-2010, 09:13 AM
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
07-29-2010, 10:01 AM
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
07-29-2010, 10:22 AM
Got it. Works perfectly (but you already knew that...)



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