Computer Hope

Software => Computer software => Topic started by: Philip123 on August 21, 2017, 10:08:51 AM

Title: Excel help
Post by: Philip123 on August 21, 2017, 10:08:51 AM

I am wanting to create a value that responds to values placed in a different table.

For example:
Week Type          Cost
1       Food         10
2       Food         12
1       Transport   15

I want the value to pick up the cost where week 1 and Food appear. How do I do that?

Title: Re: Excel help
Post by: strollin on August 21, 2017, 12:45:30 PM

If I understand your question correctly, I believe you want to use the VLOOKUP function where you  can specify which columns to search on and which values to retrieve.

Title: Re: Excel help
Post by: Mark. on August 21, 2017, 03:18:03 PM

or perhaps using SUMIFS

if you want to find the cost of all your Food for week 1, something like this would do it.

=SUMIFS(C1:C3,A1:A3,"=1",B1:B3,"=Food")

where, from your example, Week is column A, Type is column B and Cost is column C.

Title: Re: Excel help
Post by: Philip123 on August 22, 2017, 10:21:50 AM

That's great thank you!
I used the SUMIFS function and worked like a dream

Title: Re: Excel help
Post by: Mark. on August 22, 2017, 03:59:43 PM

good news, thanks for letting us know.

it's amazing what Excel can do, I'm yet to find something it couldn't do for me!

and if you are just starting out, wait till you discover macros and the VBA side of it - even better then.