# Combining IF & SUMIF Functions

#### seanm256

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

#### seanm256

##### New Member
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

### 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
Thanks for the update...I'm glad you found the issue and got it working.

Replies
1
Views
440
Replies
0
Views
80
Replies
7
Views
97
Replies
5
Views
125
Replies
1
Views
75

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.

### Which adblocker are you using?

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

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