CODEWITHSUNDEEP
excel-formulasumifs
Njolstad
Njolstad

Reputation: 1

MS EXCEL SUMIFS challenge

I want to sum all cells in a row from G7 to infinity. The criteria is dates in a month in row B7 to infinity. Is this possible?

I have tried, and came up with this:

=SUMIFS(Sum(G:G)-sum(G1:G6);sum(B:B)-sum(B1:b6);">=01.01.2016";sum(B:B)-sum(b1-B6);"<=31.01.2016")

Can anyone correct me?

FYI: All the cells in row G contains formulas, but return a number

Upvotes: 0

Views: 93

Answers (2)

Aly Abdelaziz
Aly Abdelaziz

Reputation: 290

You might want to use G6:G65536 and click OK. 65536 for Excel 2003 and earlier, 1048576 for 2007 and 2010.

For 2007-2010

=SUMIFS(sum(G6:G1048576),sum(B6:B1048576),">=01.01.2016",sum(B6:B1048576),"<=31.01.2016")

Edited: irrelevant information removed.

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

You could use this:

=SUMIFS(G7:INDEX(G:G; MATCH(1E+99;B:B));B7:INDEX(B:B; MATCH(1E+99;B:B));">=01.01.2016";B7:INDEX(B:B; MATCH(1E+99;B:B));"<=31.01.2016")

It will grow as the data grows. The INDEX/MATCH finds the last cell with a number in Column B and sets that as the last cell.

Upvotes: 1

Related Questions