Combining IF & SUMIF Functions

[seanm256]

Hello All,


So I have a table which is doing a series of calculations based off of different language criteria. I want to add an additional variable which will only run this string of calculations if another text variable is present in a different cell. If that text variable isn't present, I want it to either present a value of 0, or simply not run the string of calculations.

The idea was to have a function that I could replace the text criteria and have it distinguish between two factors.

Type A - Run SUMIFS when "Bestobell" is present only.
Type B - Run SUMIFS when "EastMall" is present only.

Reference Images:

This function is applied to the TrackingSheet2 (see .jpg) - In the column "Quantity in Stock". This formula is copied to all of the cells in Quantity in Stock. It reads the information highlighted on TrackingSheet1 (see .jpg) and tells me how much stock for each material (Sub-Category) is available. Currently, this is done via the string of SUMIF functions.

The goal is to add the variable to this string for 'TrackingSheet2' so I can collect inventory data for two separate buildings in the same spreadsheet. The formula is copied, but the cell value that changes is the 'C' column value in the function, the 'F' column value needs to read from the whole column on 'TrackingSheet1'.


Base Function:

=SUMIFS('Inventory Tracking'!$K$4:$K$10004, 'Inventory Tracking'!$G$4:$G$10004, "Received", 'Inventory Tracking'!$I$4:$I$10004, C11)+SUMIFS('Inventory Tracking'!$M$4:$M$10004, 'Inventory Tracking'!$G$4:$G$10004, "B/O Received", 'Inventory Tracking'!$I$4:$I$10004, C11)-SUMIFS('Inventory Tracking'!$N$4:$N$10004, 'Inventory Tracking'!$G$4:$G$10004, "Issued", 'Inventory Tracking'!$I$4:$I$10004, C11)-SUMIFS('Inventory Tracking'!O61:O10009, 'Inventory Tracking'!G61:G10009, "Returned", 'Inventory Tracking'!I61:I10009, C11)

With an potential IF Statement:

IF('Inventory Tracking'!$F$4:$F$10004= "Bestobell", (INSERT SUMIF ABOVE) , 0 OR "")



Obviously in this structure I cannot do that, but I was hoping maybe there is a guru out there who might be able to help me condense the formula to allow it to work, or have a better method to produce the same effect.


Any help is appreciated! Let me know if you have any questions!

Cheers,

Sean

 

[braindiesel]

The range is different in your 4th component so that will nead a tweak, but otherwise, you can add your Bestobell factor as another sumif variable

=SUMIFS('Inventory Tracking'!$K$4:$K$10004, 'Inventory Tracking'!$G$4:$G$10004, "Received", 'Inventory Tracking'!$I$4:$I$10004, C11, 'Inventory Tracking'!$F$4:$F$10004, "Bestobell")+SUMIFS('Inventory Tracking'!$M$4:$M$10004, 'Inventory Tracking'!$G$4:$G$10004, "B/O Received", 'Inventory Tracking'!$I$4:$I$10004, C11, 'Inventory Tracking'!$F$4:$F$10004,"Bestobell" )-SUMIFS('Inventory Tracking'!$N$4:$N$10004, 'Inventory Tracking'!$G$4:$G$10004, "Issued", 'Inventory Tracking'!$I$4:$I$10004, C11, 'Inventory Tracking'!$F$4:$F$10004,"Bestobell" )-SUMIFS('Inventory Tracking'!O61:O10009, 'Inventory Tracking'!G61:G10009, "Returned", 'Inventory Tracking'!I61:I10009, C11, 'Inventory Tracking'!$F$4:$F$10004,"Bestobell" )

Does that solve it?
Side note... are you dealing with 10000 rows of data, or are you being cautious, because this formula will likely run slow as it is looking at 10000 rows.
In case you are being cautious... shorthen it if you can

 

[seanm256]

Thanks for you time!

Unfortunately it does give me a #VALUE error, ill try tweaking it to see if I can get it to work, but i understand the principle of what you were going for. Ahh the 10000 rows is more just so I don't ever have to worry about that variable and changing it later, but we back-log data into a separate sheet on an annual basis so it never gets to those numbers. It would definitely bog down the system if it ever got to 10000 lines thats for sure.

 

[braindiesel]

SUMIFS('Inventory Tracking'!O61:O10009, 'Inventory Tracking'!G61:G10009, "Returned", 'Inventory Tracking'!I61:I10009, C11, 'Inventory Tracking'!$F$4:$F$10004,"Bestobell" )
These ranges not matching would likely produce an error

Re worrying about the length... I often use OFFSET to make sure my ranges are the right size... but that is a dynamic formula that can also slow things down.... depends what you are going for..
The Offset method would be
Offset (F3,1,0,counta(F:F),1) for the accurate length of $F$4:$F$10004

If I can help more, ask.

 

[KRice]

You might consider something like this...I think it accomplishes the equivalent operations and it incorporates your IF/bestobell condition. I haven't been able to test this, but respond back if you encounter an issue:

trackingsheet_20200617.xlsx
	A
1	#REF!
Sheet1

Cell Formulas
Range	Formula
A1	A1	=SUMPRODUCT(('Inventory Tracking'!$F$4:$F$10004="Bestobell")*('Inventory Tracking'!$I$4:$I$10004=C11), ('Inventory Tracking'!$K$4:$K$10004)*('Inventory Tracking'!$G$4:$G$10004="Received") + ('Inventory Tracking'!$M$4:$M$10004)*('Inventory Tracking'!$G$4:$G$10004="B/O Received") - ('Inventory Tracking'!$N$4:$N$10004)*('Inventory Tracking'!$G$4:$G$10004="Issued") - ('Inventory Tracking'!$O$4:$O$10004)*('Inventory Tracking'!$G$4:$G$10004="Returned"))

 

[KRice]

Here is a small working example that seems to check out okay. You can click on the clipboard icon in the upper left (at intersection of row and column headings) to copy for easy pasting into cell A1 of an empty worksheet and then try it to confirm that the logic is okay. The abbreviated +/- terms in I1:O1 were for my reference to keep track of which columns are added or subtracted and what Action they are associated with.

trackingsheet_20200617.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Result-->	-2						C11		+ Rec		+ B/O	- Iss	- Ret
2										Quantities
3		List of actions				Building	Action	Qty	Materials	Ordered	Rec'd	Ordered	Rec'd	Issued	Returned
4		Issued				Bestobell	Issued		A	3	2	1	2	3	3
5		Received				Bestobell	Received		A	3	2	1	2	3	3
6		B/O Received				Bestobell	B/O Received		A	3	2	1	2	3	3
7		Returned				AAA	B/O Received		A	3	2	1	2	3	3
8						Bestobell	Issued		B	3	2	1	2	3	3
9						Bestobell	Issued		B	3	2	1	2	3	3
10						Bestobell	Issued		A	3	2	1	2	3	3
11		Material-->	A
12
Inventory Tracking

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(('Inventory Tracking'!$F$4:$F$10="Bestobell")*('Inventory Tracking'!$I$4:$I$10=C11), ('Inventory Tracking'!$K$4:$K$10)*('Inventory Tracking'!$G$4:$G$10="Received") + ('Inventory Tracking'!$M$4:$M$10)*('Inventory Tracking'!$G$4:$G$10="B/O Received") - ('Inventory Tracking'!$N$4:$N$10)*('Inventory Tracking'!$G$4:$G$10="Issued") - ('Inventory Tracking'!$O$4:$O$10)*('Inventory Tracking'!$G$4:$G$10="Returned"))

 

[seanm256]

Hi Kirk,

Thank you for your time! This worked exactly the way I wanted it to. You are the excel guru I dreamed of finding. Thank you.

 

[KRice]

You're welcome. I'm happy to help...and welcome to the MrExcel board!

 

[seanm256]

Hi Kirk,

Wanted to see if I could pick your brain a bit again. So that same function you produced for me stopped working after a microsoft update. I don't know if those things are related but they happened at the same time. I did some troubleshooting, and it was coming up with a 'REF' error due to the cell range information (ex. =SUMPRODUCT(('Inventory Tracking'!$F$4:$F$10="Bestobell")*('Inventory Tracking'!$I$4:$I$10=C11) . It seems that it no longer liked reading blank cells for the function. So I reduced the amount of cells it was reading to ones that only had values. This seemed to work until I updated the amount of cells then it created another'REF' error. I have checked the cell formulas and everything should be working correctly. As far as I am aware. I don't know if you have any recommendations it would be extremely helpful.

Let me know when you get a chance.

Regards,

Sean

 

[KRice]

Hi Sean,
Could you post the exact formula that is giving an error? I've pulled up the last example I posted and extended all ranges to row 15 to introduce numerous blanks into the arrays, and everything still works okay...I can't seem to reproduce the same behavior you describe. Have you investigated the source of the error using F9?...select just a portion of the formula (let's say part of the formula is 'Inventory Tracking'!$I$4:$I$99=C11...so select that) and hit F9...you should see an array of TRUE's and FALSE's...then hit Esc to revert back to the formula...don't skip this last step as you risk overwriting this part of your formula with a hardwired array). I'm curious which part(s) of the formula might be generating the error. As you go through each part of the formula, look for where the REF error appears.