formula for summing value based on first lift number of other cells

[nader assaleh]

Hi,
how can write formula to find the summation of all values in colomn D based on the extreme lift number of column C.
many thanks

 

[Somendra Misra]

Hi Nader,

Try below formula in H9 and copy across:

=SUMPRODUCT((--LEFT($C$5:$C$19,1)=H8)*$D$5:$D$19)

Regards,

 

[nader assaleh]

many thanks

 

[Somendra Misra]

You are Welcome!!!

Regards,

 

[nader assaleh]

Hi
how can i modify this formula in a way that if i have blank cells or null cell i still get a result.
many thanks

 

[Somendra Misra]

@nader assaleh

try below array formula:

=SUM((IFERROR(--LEFT($C$5:$C$20,1),0)=H8)*--($D$5:$D$20))

Confirm with Ctrl+Shift+Enter.

Regards,

 

[nader assaleh]

works perfect.
thnksssssssss

 

[Somendra Misra]

You are Welcome!!!

Regards,