Which function is best?

C

CAPGirl

Hi
I want to capture 4 different categories that are in one column & a cost
associated to it in another column. There are numerous line items but all
identified by the category. I want to total each category based on the
dollar volume.

Which function is best to do this?
IF or VLOOKUP??

Any and all suggestions are greatly appreciated!!
Cheers,
T
 
M

Miguel Zapico

In my opinion, VLOOKUP is usually simpler to define and maintain. If you
also use named ranges, it also reads easily.
In any case, both are more intended for a cell by cell basis. If you want
to compute total try SUMPRODUCT with logical validations.

Hope this helps,
Miguel.
 
T

T Kirtley

If there are multiple occurances of the categories you are summarizing then
VLOOKUP() will not work since it will only return a match for the first row
it finds, not a sum of the values.

I think the SUMIF() function will probably work best if I understand your
post correctly.

HTH,

TK
 
A

Allllen

sheet 1:
Col A Col B
apples 15
pears 16
apples 22
bananas 12
pears 2
bananas 23

sheet2:
Col A Col B
apples =SUMIF(sheet1!A:A,A1,sheet1!B:B)
pears =SUMIF(sheet1!A:A,B1,sheet1!B:B)

- OR -

1) sort by category
2) use data > subtotals. At each change in category, use function SUM on
column dollars.
 
Top