SUMIF without helper col - possible?

[Caravishah]

Hi,

Please refer the attached sheet. I want to pull the annual numbers from the monthlies. I dont want to use offset and the want to do it without the helper coloumn (used by me for SUMIF). Is there a way out? No other volatile formulae please.

Also is there a way to do it with sumif but without the helper coloumn?

Appreciate your help!

 

[NARAYANK991]

Hi ,

Why not this ?

=SUMIFS($L6:$DC6,$L$4:$DC$4,">=" & DATE(B$4,1,1),$L$4:$DC$4,"<=" & DATE(B$4,12,31))

Narayan

 

[Hui]

B6: =SUMPRODUCT(($L6:$DC6)*(YEAR($L$4:$DC$4)=B$4))
Copy across

 

[Ashhu]

Hi ,

Why not this ?

=SUMIFS($L6:$DC6,$L$4:$DC$4,">=" & DATE(B$4,1,1),$L$4:$DC$4,"<=" & DATE(B$4,12,31))

Narayan

Smooth

 

[Ashhu]

B6: =SUMPRODUCT(($L6:$DC6)*(YEAR($L$4:$DC$4)=B$4))
Copy across

Very Smooth

 

[Caravishah]

B6: =SUMPRODUCT(($L6:$DC6)*(YEAR($L$4:$DC$4)=B$4))
Copy across

Excellent!! Thanks both of you. Amazing how sumproduct comes to the rescue! I really wonder if I know Sum product formula completely.

 

[Hui]

You may want to have a read of:
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/
Where some more great uses of Sumproduct are explained

 

[Caravishah]

You may want to have a read of:
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/
Where some more great uses of Sumproduct are explained

Thanks very much!