Excel - SUMIFS for multiple columns

excel multiple-columns sumifs
Trenera picture Trenera · Apr 12, 2016 · Viewed 48.8k times · Source

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?

Answer

cyrilb38 picture cyrilb38 · Apr 12, 2016

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.

Related questions

Multiple input box excel VBA

Right now I've been using multiple Input boxes to get user input for a couple of strings i need: Dim U As String Dim P As String U = Application.InputBox("Enter Username", "Username", Type:=1)) P = Application.InputBox("Enter Pwd", "pwd", …

Read More
In Excel, how to compare 1 cell against a column of values in two workbooks

Tried to find an answer but could not - I am an excel novice. Our company put on a webinar, we received excel spreadsheet of attendees, names company names, etc. I am tasked with comparing the Attendee list/spreadsheet with …

Read More
How to consolidate duplicates in Excel where several columns need to remain uncosolidated

I am trying to do a stock count through product stores but have several part numbers that are listed twice (Column A). When I try to consolidate these I recieve the error 'no data consolidated' which I think is caused …

Read More