CODEWITHSUNDEEP
excelexcel-formula
Willman
Willman

Reputation: 438

EXCEL Sum range based on row and column criteria

Given the Category table below:

sample

... how can I add all values in a particular category row that are in columns where the date header is <= TODAY() ?

For example, in cell B2 I want a running total of the values corresponding to the "Internet Bill" row and only columns where today (3/15) is >= the date in the column header. I highlighted these values in red just for the example.

Here's what I have so far in B2:

=SUM(INDEX($F$2:$J$4,MATCH($A2,$E2,0),MATCH(TODAY(),$F$1:$J$1,1)))

But it's only giving me "$45," which I understand correlates the greatest date value lesser than 3/15. I just can't get the INDEX formula to return a column_num range filtered by a comparison operator...

Upvotes: 0

Views: 559

Answers (2)

Ranga
Ranga

Reputation: 430

Assuming you have office 365, you can use the formula below

=IFERROR(SUM(FILTER(FILTER($B$3:$N$4,$A$3:$A$4=B7),$B$2:$N$2<=$A$1)),"Not Found")

Sample data and result

The formula also accounts for error supposing the data is not found

Supposing your data table is in another Sheet (Sheet 1), you can use formula below

=IFERROR(SUM(FILTER(FILTER(Sheet1!$B$3:$N$4,Sheet1!$A$3:$A$4=B2),Sheet1!$B$2:$N$2<=$A$1)),"Not Found")

Sample 2

Upvotes: 0

Can.U
Can.U

Reputation: 481

B2 G$1:J$1 requires date format, please change J1 to your actual range.

=SUM(OFFSET(F$1,MATCH(A2,E:E,)-1,,,COUNTIF(G$1:J$1,"<="&TODAY())))

Upvotes: 1

Related Questions