CODEWITHSUNDEEP
excelexcel-formula
adam
adam

Reputation: 424

Using Sumif and vlookup of a partial string

So basically i have a data table and a mapping table. The mapping table determines which class each of the category falls into by looking at a partial string. I then calculate the total values that falls into the same class.

While i know i can do this by simply adding in a vlookup/index match column on my data table. is there a way to accomplish the output using a single formula?

The reason is that the original data table contains other columns that a bunch of vba code relies on, therefore i do not want to amend that table if possible.

Data Table:

Name        Values
dog1        2
dog2        3
dog3        4
cat1        1
cat2        2
trout1      5
trout2      6
trout3      7

Mapping Table:

Category    Class
dog         pet
trout       fish
cat         pet

Output:

Class Total
pet   12

Upvotes: 0

Views: 692

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

It ca be made to work by array formulae but I wouldn't say it was elegant.

A fairly standard formula for listing the distinct class values

=IFERROR(INDEX(E$2:$E$10,MATCH(0,COUNTIF($G1:$G$1,$E$2:INDEX($E$2:$E$10,COUNTA($E$2:$E$10))),0)),"")

Then a SUMIF for each different class value mapped into its corresponding category values, enclosed in a SUM to get the total for all categories

=IF(G2="","",SUM(SUMIF($A$2:$A$10,"*"&IF($E$2:$E$10=G2,$D$2:$D$10,"~~~")&"*",$B$2:$B$10)))

enter image description here

Upvotes: 2

teylyn
teylyn

Reputation: 35905

Try this scenario:

Look up the category for the class with an Index/Match, append a wildcard character and put that into a Sumif function. The formula in cell H3 is as follows, copied down.

=SUMIF($A$2:$A$7,INDEX($D$3:$D$4,MATCH(G3,$E$3:$E$4,0))&"*",$B$2:$B$7)

enter image description here

Edit after comment: If multiple categories are mapped to the same class, a formula solution is no longer viable. Instead, consider using Power Pivot and the Excel data model with relationships between the tables. You will need the following tables:

  • initial data entry table with Name and value
  • a table that maps Name to Category
  • a table that maps Category to class

Then you can add these tables to the Data Model in Power Pivot and define the relationships.enter image description hereenter image description here

Now you can build a pivot table with the class from the Category/Class table that sums up the values from the initial data entry table.

Power Pivot is a free add-in for Excel 2010 to 2013 and built into enterprise versions of Excel 2016.

enter image description here

Upvotes: 3

Related Questions