Snasui Excel Forum

Hi Dear

I have a problem with SUMIFS function.i need to sum sale amounts by criteria.
I need it dynamically sum.
This possibly sum values in partial range but I need to apply SUMIFS by whole range if it according to criteria.

I attached a template with expected Result in Sheet2.


Best Regards,

Try this,

D2 in Sheet1

=LOOKUP(CHAR(255),C$1:C2)

Enter > Copy down

C3 in Sheet2

=SUMIFS('Sheet 1'!$C$2:$C$24,'Sheet 1'!$B$2:$B$24,$B3,'Sheet 1'!$D$2:$D$24,C$2&"*")

Enter > Copy to D3 then down

If C2 in Sheet2 and C1 in Sheet1 is the same value, you can remove &"*" in the above formula.

I want to ask you something why Lookup (char(255),C$1:C2)? what char(255)?? beside this char code is there anything else?

Char(255) is the last character in your machine.

This magic formula =Lookup(Char(255),Target_Range) used to find the last 'Text' in Target_Range. If you want to find the last 'Number' in Target_Range, you can use =Lookup(9.99999999999999e307,Target_Range).

Thank you so much