which formula?

[Lizzi]

Hi,
I am using excel 2016 and not sure what formula to use to get my results.

On sheet one starting in column A3 going down I have a list of names, for now it’s like this:

A3 A
A4 B
A5 C
Etc
in column B1:AF1 I have the day of the week
in column B2:AF2 I have 1st Jan all the way thru to the 31st Jan (this will be for each month of the year when its set up

On sheet 2 I have the following

Column: A B C
row 3 A $20 Mon
row 4 B $25 Fri
row 5 C $45 Wed

Basically on sheet 1 in cell C3 i want the result to be $20, so i’m trying to say "if column A says “A” (it will be a person’s name) and it falls on a Mon put $20 in that cell, if "B" (said persons name) is on Friday put $25" etc. trouble is i will have 15 peoples names and various $ amounts, so i suspect the formula will be long?

Can anyone plse help me?, i done this manually last year but hoping for some kind of formula to make it quicker, the amount of people can increase as the weeks go by

As always, thanks for any help you can provide
Lizzi

 

[Fluff]

I am using excel 2016

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about in B3 dragged down & across

=SUMIFS(Sheet2!$B:$B,Sheet2!$A:$A,$A3,Sheet2!$C:$C,B$1)

 

[Lizzi]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about in B3 dragged down & across

=SUMIFS(Sheet2!$B:$B,Sheet2!$A:$A,$A3,Sheet2!$C:$C,B$1)

many thanks

 

[Fluff]

You're welcome & thanks for the feedback.