Offset in Sumif or if

[cescko]

Hi again...

Sorry if my mind doesn't understand "offset"
i post my workbook if anyone can help me with the yellow cell(Sheets: "name" "out seller" "box") *(offset in SumIf)


Thank you in advance.

Cescko

[AliGW]

Please manually add expected outcomes to the boxes shaded yellow and then reattach the workbook.

[cescko]

Re-attach the workbook

"expected outcomes" in yellow cell

[AliGW]

No, that's not what I meant.

By 'expected outcomes', I meant the numbers that you expect the formula to return - what will the RESULTS in those yellow cells be?

Excel 2016 (Windows) 32 bit

	B	C	D	E	F	G
3		Box 01/08/2000
4	Warehouse	100
5	01/08/2000	9
6		expected outcomes	<<< What is the expected answer???
7
8
9
10
11
12
13
14
15	Box Left	91

Sheet: Box

[JohnTopley]

in "Name"

in C3

=SUMIF(OFFSET(Daily!$B$1,MATCH(Name!$B3,Daily!$B$2:$B$1000,0)+2,7,23, 1),$C$2,OFFSET(Daily!$B$1,MATCH(Name!$B3,Daily!$B$2:$B$1000,0)+2,4,23, 1))/60*15

in D3

=SUMIF(OFFSET(Daily!$B$1,MATCH(Name!$B3,Daily!$B$2:$B$1000,0)+2,7,23, 1),$D$2,OFFSET(Daily!$B$1,MATCH(Name!$B3,Daily!$B$2:$B$1000,0)+2,4,23, 1))/60*15

Copy both down

I'll look at the others shortly.

[JohnTopley]

in "Out Seller"

in D3

=IF(OFFSET(Daily!$B$1,MATCH($B3,Daily!$B$2:$B$1000,0)+28,0,1,1)=$C3, 80)

in E3

=IF(OFFSET(Daily!$B$1,MATCH($B3,Daily!$B$2:$B$1000,0)+28,0,1,1)=$C3,OFFSET(Daily!$B$1,MATCH($B3,Daily!$B$2:$B$1000,0)+28,1,1,1)*10)


in "Box"

in C5

=OFFSET(Daily!$B$1,MATCH($B5,Daily!$B$2:$B$1000,0)+30,1,1,1)

Don't understand formula (reason for) in B5

[cescko]

sheet "Box" cell "B5" will give me which date on daily if i gift a box

[cescko]

Thank you very much John!!!!!!

[JohnTopley]

It test C36 in Daily which is BLANK (or ) numeric): I think the test should be on C32 (value corresponding to label "Box").

To fill in dates in column B I assume you need to look at "Box" value and only get dates in "Daily" where "Box" value is > 0?

[cescko]

yes is right, but i need the offset to copy it down because i will do for all the year, not just a month

[cescko]

sorry my bad Wasn't c36 but C32

[JohnTopley]

In "BOX"

in B5

=IFERROR(INDEX(Daily!$B$1:$B$1000,INDEX(ROW($1:$1000),SMALL(IF((Daily!$B$1:$B$1000="box")*(Daily!$C$1:$C$1000<>0),ROW($B$1:$B$1000)-ROW($B$1)+1,""),ROWS($B$1:B1))-30)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

in C5

=INDEX(Daily!$C$2:$C$1000,MATCH(B5,Daily!$B$2:$B$1000,0)+30)

Copy both down

[JohnTopley]

Correction ...

in C5

=IFERROR(INDEX(Daily!$C$2:$C$1000,MATCH($B5,Daily!$B$2:$B$1000,0)+30),"")

[cescko]

i Don't have words!!!!

too much for my ability

Many compliments

THANK YOU

[AliGW]

Please consider clicking on the reputation star under one of John's posts - he has made a sterling effort on your behalf.

[cescko]

just to make me understand, you refer in B Colon in daily, all the colon,

but... if...

i would like to use only 3 rows or 4 rows in the colon,
is that possible?

[AliGW]

Are you wanting more help with this? If so, I'll remove the solved tag.

[JohnTopley]

I don't understand the question regarding Column B: which formula are you referring to???