SUMIFS formula possible with current worksheet formatting?

[Lil Stinker]

Hey folks-
I am looking for some formulas that will give me an accurate Stock In / Stock Out / Damaged Stock / Equip Available assessment based on how our Inventory Sheets are currently formatted. Our equipment is rental, not purchases and sales, so I need to be able to track the inventory as it goes out, comes in and what's left over in between. Below are basic examples of how the sheets are formatted. Sheet 1 shows equipment out, Sheet 2 shows equipment in and/or damaged based on the varying intervals the equipment is returned and Sheet 3 is the balance sheet with the initial count. I'm having difficulty finding a way to properly SUM the Outs and Ins so the values in blue indicate the desired results.

I've tried a variety of SUMIF/SUMIFS formulas but either end up with the wrong result or #VALUE which I suspect is resulting from my quantities being laid out across multiple columns instead of a single column in multiple rows? I've also set up dynamic named ranges since my data is not formatted in tables for each equipment type. I've been at this for days and cannot seem to find anything that resembles our setup. Short of creating separate tables and transferring the existing quantities over, I'm at a loss. I feel like this should be simple but I've always had a difficult time spotting what's right in front of my nose. Any help would be appreciated.

Sheet 1 (invOUT)

Inventory Sample.xlsx
	A	B	C	D	E	F	G	H
1	RA No	Open Date	Date Out	Exp. Return	Antennas	Surveys	Batts	Headsets
2	10000	6/2/2023	6/2/2023	6/12/2023	13	13	13	0
3	10001	6/9/2023	6/9/2023	6/19/2023	130	10	195	0
4	10002	8/1/2023	8/1/2023	8/10/2023	10	0	15	10
invOUT

Sheet 2 (invRTN)

Inventory Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	uID	RA No.	RTN No.	Open Date	Return Status	Date Out	Return Date	Antennas	DMG Ants	RTN Surveys	DMG Surveys	RTN Batts	DMG Batts	RTN Headsets	DMG Headsets
2	1	10000	00	6/13/2023		6/2/2023		0	0	0	0	0	0	0	0
3	2	10001	00	6/21/2023		6/9/2023		0	0	0	0	0	0	0	0
4	3	10000	01	6/13/2023	Full	6/2/2023	6/14/2023	13	0	13	0	13	0	0	0
5	4	10001	01	6/21/2023	Partial	6/9/2023	6/12/2023	130	1	10	0	195	0	0	0
6	5	10001	02	6/21/2023	Partial	6/12/2023	8/8/2023	0	0	0	0	0	0	0	0
invRTN

Sheet 3 (invBAL)

Inventory Sample.xlsx
	A	B	C	D	E	F
1	Quantity
2	StockItems	Total	Out	In	Dmg	Available
3	Antennas	825	10	815	1	814
4	Surveys	136	0	136	2	134
5	Batts	3280	15	3265	0	3265
6	Headsets	61	10	51	0	51
invBAL

 

[bassplr19]

Convert your ranges to tables, then it's easy

StockItems	Total	Out	In	Dmg	Available
Antennas	825	153	815	1	814
Surveys	136	23	136	0	134
Batts	3280	223	3265	0	3265
Headsets	61	10	51	0	51

Out Column -> =SUM(INDIRECT("invOUT["&[@StockItems]&"]"))
Dmg Column -> =SUM(INDIRECT("invRTN[DMG "&[@StockItems]&"]"))

 

[duggie33]

I was able to get these results using named ranges for the OUTs, RTNs, and DMGs. Setting up the named ranges was the tedious part...named range for Antennas_Out is:

=OFFSET(invOUT!$A$1,1,MATCH("Antennas",invOUT!$A$1:$H$1,0)-1,COUNTA(invOUT!$A$2:$A$1000),1)

For whatever reason, XL2BB will not bring in my named ranges. I am guessing, it is because their scope is "Workbook" rather than the worksheet.

Book1
	A	B	C	D	E	F
1	Quantity
2	StockItems	Total	Out	In	Dmg	Available
3	Antennas	825	10	815	1	814
4	Surveys	136	0	136	2	134
5	Batts	3280	15	3265	0	3265
6	Headsets	61	10	51	0	51
invBAL

Cell Formulas
Range	Formula
C3	C3	=SUM(Antennas_Out)-SUM(Antennas_Rtn)
D3:D6,F3:F6	D3	=B3-C3
E3	E3	=SUM(Antennas_Dmg)
C4	C4	=SUM(Surveys_Out)-SUM(Surveys_Rtn)
E4	E4	=SUM(Surveys_Dmg)
C5	C5	=SUM(Batts_Out)-SUM(Batts_Rtn)
E5	E5	=SUM(Batts_Dmg)
C6	C6	=SUM(Headsets_Out)-SUM(Headsets_Rtn)
E6	E6	=SUM(Headsets_Dmg)

As suggested by @bassplr19, having the data in a Table would make the task much easier.

Hope that helps,

Doug

 

[Lil Stinker]

Thank you both for your guidance. This was a classic case of overthinking on my part. I was stuck thinking only SUMIF/SUMIFS would work due to the quantities being contingent on other criteria like the dates in or out. Turns out the simplest solution is often the best.

@bassplr19 I'm sure you're right that tables would make everything easier however, I'd be hesitant to convert to tables at this point because this is just a small part of a much larger project that includes all sorts of VBA. My main concern would be breaking any of the VBA tied to these worksheets. Thank you though! I'll keep it in mind for future projects.

@duggie33 Indeed, the tedious part is all the Named Ranges which I already went through and set up. That was exhausting (and to think there are twice as many items in my full project vs. this small dataset). I also noticed my Named Ranges did not copy over from XL2BB. But your formulas are what broke the barrier I was stuck behind. I can't believe it didn't occur to me to simply sum the individual ranges and subtract the OUTs from the RTNs. Thank you so much!

 

[duggie33]

Glad we could help.

Doug