Listing unique values and summing w/o a pivot table

Hello:

I have a set of data that I'm trying to identify the unique values in a column and then sum the related quantites against each of those values:

Code Qty
22 10
23 20
22 10
24 20
22 10

Results:
Code Qty
22 30
23 20
24 20

I'm trying to do this without an advanced filter of pivot table since the data in the columns is populated over time. From what I know, the filter is a one shot run and the pivot table needs to be refreshed.

Also the data to be reviewed would be limited to a specifc array (rows/colums).

Is there a basic formula to accomplish this?

Try a SUMIF:

=SUMIF(A:A,A1,B:B)
...or...
=SUMIF(A:A,22,B:B)

In an adjacent column put this array formula, let's say you put it in C1:

=INDEX(A$1:A$100,MATCH(0,COUNTIF(A$1:A$100,"<"&A$1:A$100),0))

That's an array, so press CTRL-SHIFT-ENTER to confirm the formula. Next, in C2, enter this array formula:

=IF(COUNTIF(A$1:A$100,">"&C1), INDEX(A$1:A$100, MATCH(COUNTIF(A$1:A$100,"<="&C1), COUNTIF(A$1:A$100,"<"&A$1:A$100),0)),"")

...also confirmed with CTRL-SHIFT-ENTER. Now copy that 2nd formula down as far as needed to get all your values to appear. These two formulas not only present all your values, it presents them sorted, too.

Then do your SUMIF in D1:
=IF(C1="","",SUMIF(A:A,D1,B:B)
...copied down.

For getting unique values u can use the following formula. But this one is array formula. Must be confirmed with Ctrl+Shift+Enter

=INDEX(A1:A5,SMALL(IF(MATCH(A1:A5,A1:A5,0)=ROW(INDIRECT("1:5")),ROW(INDIRECT("1:5")),""),ROW(INDIRECT("1:5"))))

I just posted and saw the recent replies. I'lll try the recommendations and repost.

I have attached a spreadsheet to assist with the requirements.

For table u posted try

=INDEX(B3:B9,SMALL(IF(MATCH(B3:B9,B3:B9,0)=ROW(INDIRECT("1:"&ROWS(3:9))),ROW(INDIRECT("1:"&ROWS(3:9))),""),ROW(INDIRECT("1:"&ROWS(3:9)))))

Based on the worksheet u've posted

Originally Posted by Drew Goldberg

I'm trying the formula "=INDEX(A1:A5,SMALL(IF(MATCH(A1:A5,A1:A5,0)=ROW(INDIRECT("1:5")),ROW(INDIRECT("1:5")),""),ROW(INDIREC T("1:5"))))" which works when the array is set to those column cells that have a value.

However, if I extend it to a range with and without values, a #N/A is returned.

Is a modification required for cells without values?

I'm still working on the other option.

If u extend the range with or w/o values it would return N/A error. to fox it u can use this formula. This formula for workbook u posted...

=IF(ISERROR(INDEX(B3:B39,SMALL(IF(IF(ISERROR(MATCH(B3:B39,B3:B39,0)),"",MATCH(B3:B39,B3:B39,0))=ROW(INDIRECT("1:"&ROWS(3:39))),ROW(INDIRECT("1:"&ROWS(3:39))),""),ROW(INDIRECT("1:"&ROWS(3:39)))))),"",INDEX(B3:B39,SMALL(IF(IF(ISERROR(MATCH(B3:B39,B3:B39,0)),"",MATCH(B3:B39,B3:B39,0))=ROW(INDIRECT("1:"&ROWS(3:39))),ROW(INDIRECT("1:"&ROWS(3:39))),""),ROW(INDIRECT("1:"&ROWS(3:39))))))

Have you tried the two standard formulas suggested in post #4? They seem to work perfectly.

Got it!

Moving on to the second column for time - that looks more straight forward.

Actually got to run home but will resume this evening.

Thanks for the guidance.

Hi Drew, seems you have resolved but given it's (seemingly) related to the other thread I figured I'd add my 2 cents worth

You could use a SUMPRODUCT to store count of unique ID's

eg:

J1: =SUMPRODUCT(1/COUNTIF($B$3:$B$9,$B$3:$B$9&""))

Then you can populate your table without need for Arrays / Helpers using:

F3: =IF(ROWS(F$3:F3)>$J$1,"",SMALL($B$3:$B$9,1+COUNTIF($B$3:$B$9,"<="&N(F2))))

G3: =IF(N($F3),SUMIF($B$3:$B$9,$F3,$C$3:$C$9),"")

H3: =IF(N($F3),COUNTIF($B$3:$B$9,$F3),"")

all copied down as far as desired.

This is all based on the assumption that your Codes are numbers - I think this is a safe assumption.

Originally Posted by JBeaucaire

Have you tried the two standard formulas suggested in post #4? They seem to work perfectly.

Nice trick.
Very nice. Its very useful.

If by J3 you meant the SUMPRODUCT you would add a clause to discount Nulls/Blanks - it's omission was short sighted of me in the first instance, ie:

Please Login or Register  to view this content.

That said given you're always looking for numbers you could use ISNUMBER test instead (so only numbers would be considered as valid)

Please Login or Register  to view this content.

DonkeyOte:

I'll definitely consider those tests from now on as they add flexibility to the formulas.

Thank you!

Okay, here's another wrinkle:

What if there were 5 cell ranges in a column; B2:B6, B10:B14, B18:B22,... with values in between that were to be ignored.

i.e.

B2 22
B3 23
B4 22
B5 24
B6 21
B7 1002
B8 263
B9 289
B10 22
B11 23
B12 22
B13 21
B14 21
B15 1001
B16 1003
B17 872

Is this a matter of excluding the undesirable rows (if they are already known)?

You would first need to elaborate in terms of how you know certain rows are to be ignored - is this based on a valid ID range of sorts (eg 20-200) ?

Originally Posted by JBeaucaire

Have you tried the two standard formulas suggested in post #4? They seem to work perfectly.

Rehashing an old thread.

This worked fine for me, or so I thought.

In your attached file, if B3 has data and any cell from B4 down is empty, it will just keep going down the row until it finds more data

The data I am using sometimes starts with a blank cell or two in cell B3, is there a way of error checking down the column, say 4 rows, to see if there is data below an empty B3 cell

Bob

@bungslea,

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.