Adding up based on values in other columns

iksotof
3StarLounger
Posts: 288
Joined: 04 May 2010, 15:18

Adding up based on values in other columns

Post by iksotof »

Good afternoon

it has been a while since i have contacted EL, change of job etc and I'm more than rusty on Excel! No less, I have a list of services say A, B an C and adjacent their prices, say £10, £20, £30. On a sheet I have users say John, Helen, Steve in a column and on the row for each drop dons for the services. So on day one John could use Service A, day 2 Service B and so on, I would like to be able to total the amount for the services John uses. I have played around with sumifs but can't seem to get it to work. Any help would be most welcome, thank you. I attach an example, so for John's row you can see services he has used in cell C4, D4 an d R4would like his services totalled up in AH4 please. the range for the data criteria and related costs are in Services tab C3:D6

Than you very much Darren.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 69449
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Adding up based on values in other columns

Post by HansV »

Welcome back!

In AH4: =SUMPRODUCT((C4:AG4=Services!$C$4:$C$6)*Services!$D$4:$D$6)
This can be filled down.
Regards,
Hans

iksotof
3StarLounger
Posts: 288
Joined: 04 May 2010, 15:18

Re: Adding up based on values in other columns

Post by iksotof »

Thank you Hans

Nice to hear from you, super solution.

Regards
Darren.