Return Value Using SUMIF and OFFSET Function

overthinker519

New Member
Joined
Aug 30, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have a set of data source with size and quantities input horizontally. I try to return those values in G12:G17 based on criterias "HELPER" and "Size". The best way I know is to utlize functions SUMIF and OFFSET. However the size sets from data source may differ for different products. I am hoping someone could show me the way through this task. I'm open for any soluction as long as it is not VBA.

Workbook 1.xlsx
ABCDEFGHI
1HELPERPOS/AccountStyle #Color Description CodeSMLXL
2Taipei1904762WHITTaipei1904762WHITEWHIT243
3Taichung1904763BLACTaichung1904763BLACKBLAC111
4HELPERPOS/AccountStyle #Color Description CodeMLXLXXL
5Keelung1904764NATLKeelung1904764NATURALNATL354
6Kaohsiung1904765BLACKaohsiung1904765BLACKBLAC111
7
8
9HELPERPOS/AccountStyle #Color Description CodeSizeQty
10Taipei1904762WHITTaipei1904762WHITEWHITS2
11Taipei1904762WHITTaipei1904762WHITEWHITM4
12Taipei1904762WHITTaipei1904762WHITEWHITL3
13Kaohsiung1904765BLACKaohsiung1904765BLACKBLACM2
14Kaohsiung1904765BLACKaohsiung1904765BLACKBLACL0
15Kaohsiung1904765BLACKaohsiung1904765BLACKBLACXL0
Sheet1
Cell Formulas
RangeFormula
A2:A3,A10:A15,A5:A6A2=B2&C2&E2
G10:G15G10=SUMIF($A$2:$A$6,A10,OFFSET($A$1,MATCH($A10,$A$2:$A$6,0),MATCH($F10,$A$1:$I$1,0)-1,7,1))


Thank you for your time.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Here is one idea that doesn't require concatenating the columns:
Book2
ABCDEFGHI
1HELPERPOS/AccountStyle #Color Description CodeSMLXL
2Taipei1904762WHITTaipei1904762WHITEWHIT243
3Taipei1904762WHITTaipei1904762WHITEWHIT3542
4Taichung1904763BLACTaichung1904763BLACKBLAC111
5HELPERPOS/AccountStyle #Color Description CodeMLXLXXL
6Keelung1904764NATLKeelung1904764NATURALNATL354
7Kaohsiung1904765BLACKaohsiung1904765BLACKBLAC111
8
9
10HELPERPOS/AccountStyle #Color Description CodeSizeQty
11Taipei1904762WHITEWHITS5
12Taipei1904762WHITEWHITM9
13Taipei1904762WHITEWHITL7
14Taipei1904762WHITEWHITXL2
15Taichung1904763BLACXL1
16Kaohsiung1904765BLACKBLACM1
17Kaohsiung1904765BLACKBLACL1
18Kaohsiung1904765BLACKBLACXL0
Sheet1
Cell Formulas
RangeFormula
A2:A4,A6:A7A2=B2&C2&E2
G11:G18G11=IFERROR(SUM(FILTER(FILTER($F$2:$I$7,($B$2:$B$7=B11)*($C$2:$C$7=C11)*($E$2:$E$7=E11)),$F$1:$I$1=F11)),"")
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top