SUMIFS enitire row as the criteria rage?

[jackfox68]

Is it possible to use an entire row, for example (D:D) instead of defining the range, again for example (D4:D45). I am unsure of the amount of data points that will be entered into the criteria range and would prefer to define the entire row as the criteria range.

Am I making sense?

I am getting better at this stuff - one little battle at a time. Great resource we have here.

 

[jim may]

D:D is a reference to a COLUMN, not a entire Row.

Row("2:2") would be a reference to an entire Row.

 

[jim may]

Make that...

Rows("2:2") would be a reference to an entire Row

 

[T_Holliday]

Not as far as I know, you can't. Or, in my own experimentation, it doesn't work the way I think you want it to. When I tried it, even though I specified B:B as my Criteria range, it only checked the value of the row that the SUMIF formula itself was on. So, if the formula is at D1, where A1:A20 all equal "1" and B1:B20 all equal "2" then I get 0. But if I change B1 to "2" then I suddenly get "20" as a result. Excel's logic there is that since A1=B1, then it adds all values from A:A.

The reason is that Criteria needs to be specific. So, it can reference a single cell which contains the value you want to match (such as summarizing a larger table of data) or it can be a specific, explicit criteria that you specify in the formula. Excel won't accept more than one criteria for each formula in those conditions.

Are you looking for the sum of A:A as long as the values in A:A are greater (or less than) the corresponding values in B:B?

 

[Aladin Akyurek]

Is it possible to use an entire row, for example (D:D) instead of defining the range, again for example (D4:D45). I am unsure of the amount of data points that will be entered into the criteria range and would prefer to define the entire row as the criteria range.

Am I making sense?

I am getting better at this stuff - one little battle at a time. Great resource we have here.

Would you write down how the formula is intended to look?