sumif or sumifs values less than corresponding values in the adjacent column

Hi,

I am trying to sum all values in a column less than their corresponding values in other column. Can someone please help me figure out how to formulate this? i have used the function SUMIFS(H3:H72,$D3:$D72,"<"&H3:H72)
attached a snapshot. Appreciate the forum's help!


thanks,
harika

Try,

=SUMPRODUCT(--($H$3:$H$72>$D$3:$D$72)*($H$3:$H$72))

Hi

Use the array formula

Formula:

Please Login or Register  to view this content.

if data in D2:H54

Best regards

Thank you for quick response!

Berlan, the formula worked well but it returns "#VALUE!" if one of the cells has no value i.e if it's empty. does the function need to be modified to match that? Appreciate your help!

Jose, the function returns to "#VALUE!". Appreciate your help!

Thank you for quick response!

Berlan, the formula worked well but it returns "#VALUE!" if one of the cells has no value i.e if it's empty. does the function need to be modified to match that? Appreciate your help!

Jose, the function returns to "#VALUE!". Appreciate your help!