CODEWITHSUNDEEP
excel
Jesuraja
Jesuraja

Reputation: 3844

Consistent formula in Cell

In Excel, I want to make consistant formula in a cell.

  | A     | B  |C
--|-------|----|----------------------------------------
1 | Date1 | 12 |=B1
2 | Date1 | 45 |=B2+B1
3 | Date1 | 20 |=SUMIF(A1:A100, A3, B1:B100)
4 | Date2 | 10 |=B4
5 | Date2 |  5 |=B4+B5
6 | Date2 | 28 |=B4+B5+B6
7 | Date2 | 88 |=SUMIF(A1:A100, A7, B1:B100)

I used different formulas in cell C for each row. How to make Consistent Formula in Cell C?

Is it possible to select particular range in SUMIF function? e.g. Instead of SUMIF(A1:A100, A7, B1:B100), I want SUMIF(A1:A100, A7, B4:B7)

Upvotes: 0

Views: 59

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

This single formula will do it

=SUMIFS($B$1:B1,$A$1:A1,A1)

Enter in C1, copy down as far as required.

Note the $'s. These anchor the Criteria Range and Sum Range at row 1. When the formula is copied down these ranges will expand down to the row the formula is in.

Upvotes: 1

Related Questions