Combining IF & SUMIF Functions

seanm256

New Member
Joined
Jun 17, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • TrackingSheet1.JPG
    TrackingSheet1.JPG
    65.3 KB · Views: 14
  • TrackingSheet2.JPG
    TrackingSheet2.JPG
    37.8 KB · Views: 16

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
RangeFormula
A1A1=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"))
 
Upvote 0
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
ABCDEFGHIJKLMNO
1Result-->-2C11+ Rec+ B/O- Iss- Ret
2Quantities
3List of actionsBuildingActionQtyMaterialsOrderedRec'dOrderedRec'dIssuedReturned
4IssuedBestobellIssuedA321233
5ReceivedBestobellReceivedA321233
6B/O ReceivedBestobellB/O ReceivedA321233
7ReturnedAAAB/O ReceivedA321233
8BestobellIssuedB321233
9BestobellIssuedB321233
10BestobellIssuedA321233
11Material-->A
12
Inventory Tracking
Cell Formulas
RangeFormula
C1C1=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"))
 
Upvote 0
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.
 
Upvote 0
You're welcome. I'm happy to help...and welcome to the MrExcel board!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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