SUMIFS Formula coming up #Value

Hi i have used this with sumIF and it worked but trying it with SUMIFS it comes up #Value

=SUMIFS('Monthly View'!D4:AA7,'Monthly View'!D3:AA3,"=In")

I want to sum the values in D4:AA7, if the value in D3:AA3 is equal to In

Think of a inventory system

Monthly View has columns in and Out i want to add all the parts that are received for the year and subtract them from all the parts issued for the year.

As this seemed complicated i started by just calculating the Received parts and will do the same for issued parts then once the totals were calculated do the subtraction.


Any help is appreciated

You may need to read up about SUMIFS;

https://support.office.com/en-us/art...6-611cebce642b

In particular;

Understand the difference between SUMIF and SUMIFS.

The order of arguments differ between SUMIFS and SUMIF. In particular, the sum_range argument is the first argument in SUMIFS, but it is the third argument in SUMIF. This is a common source of problems using these functions.

If you're copying and editing these similar functions, make sure you put the arguments in the correct order.

Originally Posted by FlameRetired

Also with SUMIFS the sum_range and criteria ranges must be of the same dimensions. D4:AA7 has 4 rows D3:AA3 has one.

The probable reason this worked with SUMIF has to do with a unique property of SUMIF. It will redimension the ranges to match. I am guessing that is what happened earlier.

In XOR LX's blog he mentions this about half way down the page.

How do i sum values in multiple columns with multiple criteria. The 1 row formula in where i am storing in and out as the values.

The range that is 4 rows has the data that i want to add ?

@UltimateNeo
BTW,please don't quote entire posts unnecessarily. They are just clutter and make threads hard to read.
Use the "Quick reply" instead
Thanks

Sorry i was on my phone, so i cant finds all the options in the mobile version of the website.

I clicked reply and it just did that