Sumif where condition is only a part of a cell

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hi,

I am trying to sum a column that evaluate a criteria in
another column. The only problem is that I just want to
evaluate the first 6 characters in the cell. I have tried
the function below, but that did not work.

I dont think I can use SUMPRODUCT either because I the
table is several hundreds rows.

=SUMIF(Left(Data!$B$1:$B$500;6);"noksek";Data!$L$1:$L$500)

Anyone have any suggestions to come around this problem?

Peter
 
Peter said:
Hi,

I am trying to sum a column that evaluate a criteria in
another column. The only problem is that I just want to
evaluate the first 6 characters in the cell. I have tried
the function below, but that did not work.

I dont think I can use SUMPRODUCT either because I the
table is several hundreds rows.

=SUMIF(Left(Data!$B$1:$B$500;6);"noksek";Data!$L$1:$L$500)

Anyone have any suggestions to come around this problem?

Peter
Click to expand...

SUMIF can only take a range as its first argument (such as your
Data!$B$1:$B$500), not a function of a range (such as your
Left(Data!$B$1:$B$500;6)). But why do you think you can't use SUMPRODUCT? It
will work on any range length up to 65535 rows long. Either of the formulas
below will work. (But check first - is your argument separator ";" rather
than "," ?)

=SUMPRODUCT((LEFT(Data!$B$1:$B$500,6)="noksek")*Data!$L$1:$L$500)
or
=SUMPRODUCT(--(LEFT(Data!$B$1:$B$500,6)="noksek"),Data!$L$1:$L$500)

As a sideline, I virtually never use SUMIF but I use SUMPRODUCT extensively.
As far as I am aware, there is nothing that SUMIF can do that SUMPRODUCT
cannot do, and (of course) the latter is much more versatile.
 
Try this:

=SUMIF(Data!$B$1:$B$500;"noksek*";Data!$L$1:$L$500)

I have taken the LEFT function out of the first argument and added a "*"
wildcard to the second (criteria) argument.

--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
Paul,

Thanks,

My computer have norwegian systemsettings, so it uses ;
instead of the ,.

Your first function did not work for some reason, and
that is the same function as I have tried earlier.
The second one worked perfectly, but what is the two --
that you are using in the function (before the LEFT
funtion)

Peter
 
Simply two minus signs.

LEFT(Data!$B$1:$B$500,6)="noksek"
will produce an array of boolean values (TRUE/FALSE), whereas SUMPRODUCT
expects numeric arguments. The purpose of -- is just to convert TRUE or
FALSE to 1 or 0. It is the same as multiplying by -1 twice.

The first formula should have accomplished this automatically. It works for
me; sorry, I don't know why it doesn't work for you. Another alternative
would be
=SUMPRODUCT((LEFT(Data!$B$1:$B$500,6)="noksek")*1,Data!$L$1:$L$500)
but I suggest you stick with the one that works!
 
Back
Top