I need to sum the values of several columns, if other cells in the same row match a predefined criteria. The working formula for only 3 columns is the following:

=SUM(SUMIFS(‘Sheet1'!W:W; ‘Sheet1'!$B:$B;"Sales";‘Sheet1'!$C:$C;">=4");SUMIFS(‘Sheet1'!X:X; ‘Sheet1'!$B:$B;"Sales";‘Sheet1'!$C:$C;">=4");SUMIFS(‘Sheet1'!Y:Y; ‘Sheet1'!$B:$B;"Sales";‘Sheet1'!$C:$C;">=4"))

I will need to use the formula for several cells (and sum more than 10 columns per time) and I will need to change the columns manually, so I need the same formula in the following way:

=SUMIFS(‘Sheet1'!W:Y; ‘Sheet1'!$B:$B;"Sales";‘Sheet1'!$C:$C;">=4")

,but currently this formula leads to a "#VALUE!" error. The reason for that is (I assume) the use of multiple columns "W:Y" Can you suggest a workaround?

3 Answers

I would suggest to use SUMPRODUCT rather than SUMIFS. You can build something like that :

=SUMPRODUCT((B1:B1048575="Sales")*(C1:C1048575>=4)*(W1:Y1048575))

The downside of SUMPRODUCT is that you can't use a whole column (for example you cannot write SUMPRODUCT((B:B="Sales"...)), this would generate an error).

Hope this helps.

0

I suggest you add a column with the sum('sheet1'!W:Y) and then use sumifs on this columns. It is a two step way but it will give the result you expect

1

Here's what I have :)

=SUM(SUMIFS('WTD Raw'!R:R,'WTD Raw'!E:E,"Kindle-Customer Care",'WTD Raw'!J:J,"Week27",'WTD Raw'!H:H,'PassRate | July'!G8) + SUMIFS('WTD Raw'!R:R,'WTD Raw'!E:E,"Kindle-Technical Support",'WTD Raw'!J:J,"Week27",'WTD Raw'!H:H,'PassRate | July'!G8))

Instead of using ";" use the Mathematical Operators for it to work.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy