Total QUANTITY of most common ITEM of certain CLASS

**galen** · 05-16-2010, 03:27 PM

Hi,

i'm a bit stuck with a List in Excel

.

I need the Total QUANTITY of the most common ITEM of a certain CLASS.

This is an example part i used for testing.

Please Login or Register  to view this content.

I can specify the CLASS and need to get the most common ITEM of the chosen CLASS (which would be "banana" in this case) an then sum the different QUANTITY (which should add up to "11")

I tried with VLookup, Index, Match etc... but i'm at an end ... Gave me lots of headaches already.

The Excel file is attached and I'm using Excel 2003.

Any help is appreciated.

galen

**zbor** · 05-16-2010, 04:16 PM

Try look here (result is different because I've changed input data but play a little while with it and see does it work

)

Edit: This is solution that sums only ITEM for one CLASS.

If you need to SUM no matter of CLASS use =SUMIF(D3:D12,D29,C3:C12) in c29

**zbor** · 05-16-2010, 04:26 PM

Also, in Excel options -> Formulas turn on Automatic

Edit: Slightly improved to give ITEM solution if no match (instead of apple in first case).

**zbor** · 05-16-2010, 05:25 PM

Sorry, two above solutions doesn't work so I've start working on this one but don't have time to finish it...

Things to do:

- Instead of ROW($A$1:$A$5) needs to go to MAX LEN of the word
- get rid of 3 helper columns

**galen** · 05-17-2010, 12:59 AM

I've just put the formulas in my file and I got weird results.
Half of the values come out right, the other half doesn't.

Seems that getting most common ITEM of a CLASS does not work right.
The QUANTITY adds sums fine.

I'll give it another try tonight.

Thanks

**zbor** · 05-17-2010, 02:07 AM

MAybe this.. With aditional column...

I think I've uploaded wrong workbook last night... Sorry, it was late

Note: It's and array formula in D29 and you must enter it with ctrl+shift+enter

**DonkeyOte** · 05-17-2010, 08:02 AM

Originally Posted by galen

I can specify the CLASS and need to get the most common ITEM of the chosen CLASS (which would be "banana" in this case) an then sum the different QUANTITY (which should add up to "11")

If as implied by your sample file the Quantity is

a) independent of chosen Class (ie all banana not just banana in Class B)

b) class is manually determined

Then, using your original sample file:

C32:
=SUMIF($D$3:$D$12;$D32;$C$3:$C$12)
confirmed with Enter

D32:
=INDEX($D$3:$D$12;MODE(IF($E$3:$E$12=$B32;MATCH($D$3:$D$12;$D$3:$D$12;0))))
confirmed with CTRL + SHIFT + ENTER

If the quantity is meant to account for class also then use SUMPRODUCT in place of SUMIF (zbor's file illustrates this already).