# sumifs, vlookup, multiple column rules

1. ## sumifs, vlookup, multiple column rules

Hi,

In my spreadsheet I have 1 column with a range of different values, one with either "a" "b" or "c", one with "yes" or blank, and one with number 1-7.

Column 1 are the values i want to sum together but on some conditions...
- only include the value in the sum if the 3rd column is blank (omit if reads "yes")
- only sum together values which have the same number/letter in 2nd and 4th column (ie all A1's, all B5's etc)

I want to write a formula to sum these so that when the sort of the table changes the calculation doesn't change.

Workbook2.xlsx

2. ## Re: sumifs, vlookup, multiple column rules

This formula should work, but you will have to use one formula for each possible combinations of the Type & Number columns.

Type/Number Formula
A0 =SUMIFS(A:A,B:B,"A",C:C,"<>YES",D:D,0)
A1 =SUMIFS(A:A,B:B,"A",C:C,"<>YES",D:D,1)
A2 =SUMIFS(A:A,B:B,"A",C:C,"<>YES",D:D,2)
A3 =SUMIFS(A:A,B:B,"A",C:C,"<>YES",D:D,3)
...
B0 =SUMIFS(A:A,B:B,"B",C:C,"<>YES",D:D,0)
B1 =SUMIFS(A:A,B:B,"B",C:C,"<>YES",D:D,1)
B2 =SUMIFS(A:A,B:B,"B",C:C,"<>YES",D:D,2)
...

Regards

3. ## Re: sumifs, vlookup, multiple column rules

This works as I understand your problem. It uses two helper columns that can be hidden.

There are currently 1 users browsing this thread. (0 members and 1 guests)