Adding a Match to an Sumif formula

[brandybartz]

I have the below existing formula that I need to now add a second section to tell it to match to values in a different workbook and am not sure how to structure the formula.

Existing formula

=SUMIFS($U6:$BU6,$U$2:$B$2,">="&$D6,$U$2:$BU2,"<="&$E6)

New formula

=SUMIFS(MATCH $C2, NEW WORKSHEET RANGE, $U:$B),".="&$D6,NEW WORKSHEET RANGE $U$2:$BU$2,"<="&$E6)

 

[BGY23]

Hi,
You'll want something like this. You can change the cells to fit your ranges.

[TABLE="width: 346"]
<colgroup><col width="461" style="width: 346pt; mso-width-source: userset; mso-width-alt: 16859;"> <tbody>[TR]
[TD="width: 461, bgcolor: transparent"]=SUM(IF($B$6:$U$6>=$D6,IF($B$6:$U$6<=$E6,$B$2:$U$2)))

I don't think Sumifs work between workbooks unless both workbooks are open.

You can pick p the ranges by highlighting them in each workbook and the formula will require a Crl Shift and Enter key all pressed together on entering to make it work. You'll get {and } at each end of the formula if you've done it properly

[/TD]
[/TR]
</tbody>[/TABLE]

 

[brandybartz]

[TABLE="width: 637"]
<colgroup><col width="849" style="width: 637pt; mso-width-source: userset; mso-width-alt: 31049;"> <tbody>[TR]
[TD="width: 849, bgcolor: transparent"]I am able to get the below to return the correct values but I need it to do a match instead of me telling which row to look at

=SUMIFS('Item Types ST Weekly'!$D7:$BD7,'Item Types ST Weekly'!$D$2:$BD$2,">="&$D7,'Item Types ST Weekly'!$D$2:$BD$2,"<="&$E7)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]** MATCH($C7,'Item Types ST Weekly'!$A$5:$A$2357)
[/TD]
[/TR]
</tbody>[/TABLE]

 

[BGY23]

Match and sumif are two different animals. Match looks for a descrete value and sumif looks for multiples. Maybe if you can give a sample of your data and explain what you want to achieve I can see what you want and maybe how to get what you want.

 

[brandybartz]

I figured out the formula. Here is the answer for anyone else who has this problem.

=IFERROR(SUMIFS(INDEX('Item Types ST Weekly'!$E:$BE,MATCH('Item ST Weekly'!$F5,'Item Types ST Weekly'!$A:$A,0),0),'Item Types ST Weekly'!$E$2:$BE$2,">="&$G5,'Item Types ST Weekly'!$E$2:$BE$2,"<="&$H5),"HELP")