Google Sheets- Formula SUMIFS with cell reference doesn't give value

Question

Google Sheet Screen

HI,

When i write this formula IT WORKS:

=SUMIFS($D$4:$D$9,$A$4:$A$9,B1,$B$4:$B$9,B2)

But when i write it differently, there is no result:

=SUMIFS($e$4:$e$9,$A$4:$A$9,B1,$B$4:$B$9,"B"&"2")

The result is 0.

Even when i try to reference it with row formula, As image shows, it doesn't give result:

=SUMIFS($D$4:$D$9,$A$4:$A$9,B1,$B$4:$B$9,"B"&row())

I expected to get the same result as the first code

=SUMIFS($D$4:$D$9,$A$4:$A$9,B1,$B$4:$B$9,B2)

Any ideas why it doesn't work?

Thanx!

Answer 1

You must use this =SUMIFS($D$4:$D$9,$A$4:$A$9,B1,$B$4:$B$9,INDIRECT("B"&"2")) to make it work.