Sum all values depending on offset value

[TimClayton]

Hi all

I'm not sure how to sum up the nature of my problem, sorry if the heading is confusing. I'll try to keep it as clear as possible.

After some processes, I end up with several tables of data in a sheet in the following format:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Billy[/TD]
[TD]STAFF MEMBER[/TD]
[/TR]
[TR]
[TD]
STOCK NUMBER[/TD]
[TD]
PRICE[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]£100[/TD]
[/TR]
[TR]
[TD]2222[/TD]
[TD]£200[/TD]
[/TR]
[TR]
[TD]
TOTAL[/TD]
[TD]
£300[/TD]
[/TR]
</tbody>[/TABLE]











[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Billy[/TD]
[TD]STAFF MEMBER[/TD]
[/TR]
[TR]
[TD]
STOCK NUMBER[/TD]
[TD]
PRICE[/TD]
[/TR]
[TR]
[TD]3333[/TD]
[TD]£300[/TD]
[/TR]
[TR]
[TD]4444[/TD]
[TD]£400[/TD]
[/TR]
[TR]
[TD]
TOTAL[/TD]
[TD]
£700[/TD]
[/TR]
</tbody>[/TABLE]











This would carry on down and include 6 other salespeople and their sales.

What I want is a formula that looks for all the "Billy"s and sums all the "Total Price"s. Then I could just copy the formula and change the name to "Linda" and it would show all the sales for her. In my head this would be something like:

Search column A for "SPECIFIC SALESPERSON NAME", then sum all values that are offset by 1,4 from each "SPECIFIC SALESPERSON NAME" found

I just have no idea how to do that... Any help would be greatly appreciated!

 

[steve the fish]

Its always 4 cells below the name?? You have to be certain or this isnt going to work but:

=SUMIFS($B$5:$B$104,$A$1:$A$100,C1)

with the name to search for in C1.

 

[TimClayton]

Hi Steve

Thanks for the reply. The data I gave was a simplified version of my data. The offset is actually 4,16. But I can just adjust the formula accordingly.

However, I tried your formula on the test data and it doesn't seem to work:

I copied and pasted your formula into C2. I entered the name to search for into C1. Am I missing something?

 

[steve the fish]

Yes you can't sum text. £700 looks like it's text. Change it to 700 and then format the cell to currency. The pound sign shouldn't be part of the cell value or it won't sum

 

[TimClayton]

Well that's a little embarrassing! You formula certainly does work! Thank you for your help!