Alternative method to using sumif and vlookup

[bkholy]

I have ten rows with different data. The intent is to create another column and use the vlookup function to look up the category that the product is in the first column. I then will use a sumif to add up to a bigger category name based on the results I will receive from vlookup. Been trying to figure out for a while, if there is a short cut for this, so i don't have to create a second column to do individual vlookups on each row.


Column A Column B
Product A Vlookup(Product A, lookup array, 2)=catergory A
Product B Vlookup(Product A, lookup array, 2)=catergory A
Product C Vlookup(Product A, lookup array, 2)=catergory C
Product D Vlookup(Product A, lookup array, 2)=catergory D
Product E Vlookup(Product A, lookup array, 2)=catergory D
Product F Vlookup(Product A, lookup array, 2)=catergory A

Category A = SUMIF(the result of vlookup, A
Category B = SUMIF(the result of vlookup, B
Category C = SUMIF(the result of vlookup, C
Category D = SUMIF(the result of vlookup, D

Goal is to the sumif with one formula, without creating Column B

Any suggestions would be great!

Thank you,
Boris

[daffodil11]

You can use SUMIFS or SUMPRODUCT.

=SUMIFS(sum range, critieria range 1, criteria2, criteria range2, criteria2)

=SUMPRODUCT((sum range)*(critieria range 1=criteria1)*(criteria range2=criteria2))

[Jean.P28]

post a example

[bkholy]

The problem with your suggestion is that I can't create the criteria range with one vlookup function. Does that make sense?

[bkholy]

post a example

There is an example in my original post.

[daffodil11]

So use the more flexible variant, LOOKUP or SUMPRODUCT, which allows you to nest as many functions and criteria within it as you want.

There shouldn't be any need for VLOOKUP at all.

Can you create small example to illustrate your need?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[bkholy]

So use the more flexible variant, LOOKUP or SUMPRODUCT, which allows you to nest as many functions and criteria within it as you want.

There shouldn't be any need for VLOOKUP at all.

Can you create small example to illustrate your need?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Book2.xlsx

Please see attached for example.

[daffodil11]

Please Login or Register  to view this content.

[bkholy]

Please Login or Register  to view this content.

Sorry, maybe I didn't explain myself. I don't want to use STEP 2. I want to automate inside a function.

[daffodil11]

In your attachment you have a line pointing to Column D as what you don't want.

You need some sort of reference or index that indicates what each Product Category is; even if you're going to skip a few steps in between you need at least some sort of interpretation.

[FlameRetired]

Sorry, maybe I didn't explain myself. I don't want to use STEP 2. I want to automate inside a function.

OK. Then does this do what you want?

Please Login or Register  to view this content.

[bkholy]

In your attachment you have a line pointing to Column D as what you don't want.

You need some sort of reference or index that indicates what each Product Category is; even if you're going to skip a few steps in between you need at least some sort of interpretation.

Apologies, what i meant to say, is I want to skip the step in STEP 1, in which I have to do a vlookup for each row. You are right, I still need the table mapping in STEP 2.

Does that make sense?

-Boris

[daffodil11]

Here's the example again, without Column D.

[bkholy]

Here's the example again, without Column D.

Nicely Done! Exactly what I was looking for. I didn't realize I could do that with Sumproduct formula. In a way, this has the same functionality as vlookup, but instead with sumproduct. Thanks again for the prompt response!