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: 7
  • TrackingSheet2.JPG
    TrackingSheet2.JPG
    37.8 KB · Views: 7

seanm256

New Member
Joined
Jun 17, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
As always great advice. I have a series of hidden columns that I realistically should have checked. There was a #REF error in one of the more recent entries. I think due to incorrect data input. So it was causing the functions to error out. I will have to remember to use F9 more frequently for auditing my excel sheets. Thanks again.

Regards,

Sean
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,063
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the update...I'm glad you found the issue and got it working.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,326
Messages
5,624,019
Members
416,006
Latest member
PCaffrey

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
Top