if column A has multiple text values that start with xyz i want the corresponding figures in col B to be added

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
840
Office Version
  1. 2013
Platform
  1. Windows
i just wrote an excel test for an interview. something which should've taken 10-20 minutes took me 80. oh dear. anyway let me clarify the things here i was having trouble with so the next time i'm ok.

in my data worksheet there was Labour Expense 1 and Labour Expense 2.
in the main sheet I was dealing with I just had labour expense. how would i get the sum of both exp1 and exp2 in there? now of course if i did it one by one this would be no problem but we want this to be in a way that can be duplicated easily, so by using vlookups or sumif statements. b/c in the data sheet there was also raw material expense 1 and RM expense 2, then rent 1 and rent 2, etc. now in this case there was only 1 and 2, but what if there had been a third, is there any simple way of dealing w/ that as well? i guess the simple way of doing this was to say - if in this range (ie column A) there's a value with starts off with Labour Expense I want the corresponding figure (in column B) to be added.

also in the data tab i had values for north, south, and east, but not west. when i created a vlookup to import those values in, west gave n/a instead of 0. which woulda been OK except the main worksheet then tried to calculate a total and average, both of which got messed up b/c of the n/a. what was the right way to tackle this?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
bigdan said:
In my data worksheet there was Labour Expense 1 and Labour Expense 2.
in the main sheet I was dealing with I just had labour expense. how would i get the sum of both exp1 and exp2 in there?

You can use wildcards in SUMIF

Code:
=SUMIF(Data!$A:$A,$A1&"*",Data!$B:$B)

where Data!A holds labels and Data!B holds values and A1 holds criteria: Labour Expense

bidgan said:
also in the data tab i had values for north, south, and east, but not west. when i created a vlookup to import those values in, west gave n/a instead of 0. which woulda been OK except the main worksheet then tried to calculate a total and average, both of which got messed up b/c of the n/a. what was the right way to tackle this?

depends on the nature of the lookup but I suspect another SUMIF would have sufficed (would generate 0 for non matching item)
if aggregation not possible then if using XL2007+ see IFERROR ... pre XL2007 ISERROR, ISNUMBER(MATCH(...)), COUNTIF etc... ie all means by which to either handle the error or test that match exists before conducting the lookup.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top