Multi-Layered lookup

[Victtor]

the basic formula I use is this:

=SUMIF(A:A,$A$10,B:B) in this example, $A$10 returns "Total Net Sales" and there is a dollar amount in column B

but...I need to sum all instances of $A$10 only if the cell 3 spots above each instance = "Saturday"

I hope this is clear.

the first instance of "Total Net Sales" occurs on A10. However in this case, the cell 3 spots above it, A7, contains "Thursday" so I do not want the corresponding number in Column B summed.


however,


another instance of "Total Net Sales" occurs on A97. However in this case, the cell 3 spots above it, A94, contains "Saturday" so I WANT the corresponding number in Column B summed.


The ultimate goal of this is to use the formula to sum the data for all Saturdays and all Sundays only. So if you can write the formula to extract the sales for Saturday and Sunday, I would be grateful

 

[ttray33y]

i maybe wrong but have you tried SUMIFS?

 

[Aladin Akyurek]

Needs to be done with care:

=SUMIFS(B5:B28,A2:A25,"saturday",A5:A28,"total net sales")

where start and end cells of range specifications must offset with respect to each other.

 

[Victtor]

Needs to be done with care:

=SUMIFS(B5:B28,A2:A25,"saturday",A5:A28,"total net sales")

where start and end cells of range specifications must offset with respect to each other.

This looks like it is started in the right direction. However, I have hundreds of rows of data. How do I apply this to the entire column and sum every instance?

 

[Victtor]

Actually, I was able to make this work! thanks!

I used this version to get Saturday and Sunday:

=(SUMIFS(B5:B1303,A2:A1300,"saturday",A5:A1303,"total net sales"))+(SUMIFS(B5:B1303,A2:A1300,"Sunday",A5:A1303,"total net sales"))

 

[Aladin Akyurek]

Actually, I was able to make this work! thanks!

You are welcome.

I used this version to get Saturday and Sunday:

=(SUMIFS(B5:B1303,A2:A1300,"saturday",A5:A1303,"total net sales"))+(SUMIFS(B5:B1303,A2:A1300,"Sunday",A5:A1303,"total net sales"))

You can contract that to:

=SUM(SUMIFS(B5:B1303,A2:A1300,{"saturday","sunday"},A5:A1303,"total net sales"))