Sumif with left

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:56 PM GMT

Question

SUM(IF(LEFT($A$12:$A$30,3)="UTL",$H$12:$H$30))

Will not pickup from the following:

A12 G12 UTL SMUD PAYMENT $101

Answer

Bill,

You didn't ask me this question - I found it in the question pool


If I put in the data as you show and enter the formula as an array formula, then you are correct, I get a value of zero. But, you say your value is in column G and you formula is adding values in column H. If I move the $101 to H12 it works or if I change the formula to


=SUM(IF(LEFT($A$12:$A$30,3)="UTL",$G$12:$G$30)) (Array entered with ctrl+shifft+Enter)

then it also works.


So possibly a typo that you have overlooked.

Or if you are getting a #Value result, then that would indicate you didn't enter the formula as an array formula using Ctrl+Shift+Enter.

Hope this clears it up for you.

Advertisement

©2017 eLuminary LLC. All rights reserved.