# Excel 2013 Formula: =sumif(left(

#### jedwardo

##### Board Regular
What am I doing wrong with this thing?

Code:
``=sumif(left(a6:a60, 1), "A", E6:E60)``

it all seems to line up in "insert function" but when I press enter it says to check insert function or add an apostrophe if I'm not trying to type a function.

trying to sum column E if first letter in column A is "A"

Thanks,
Jordan

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi.

Unfortunately the range parameter passed to SUMIF must be a reference to an actual worksheet range.

Either:

=SUMIF(A6:A60,"A*",E6:E60)

or

=SUMPRODUCT(0+(LEFT(A6:A60)= "A"),E6:E60)

Regards

=SUM(IF(LEFT(A6:A60,1)="A",E6:E60))

Array Formula, when ENTER you need to press CTRL-SHIFT-ENTER button together

Hi, Try =SUM(IF(LEFT(A6:A60,1)="a",E6:E60,0)) Ctrl + Shift + Enter not just enter on a PC or Command + Return on a MAC
beaten by azumi ...

All 4 of those options worked, thank you much everyone. Is the 2nd option using multiplication? I never would have thought of that

Jordan

Last edited:
Is there a way to make one of these return "" instead of 0 for the columns that had nothing to add? Tried modifying the 4th option to this but it's not happening for me. Using ctrl/shft/enter. Pain to do on a tablet lol

Code:
``[COLOR=#333333]=SUM(IF(LEFT(A6:A60,1)="A",E6:E60,""))[/COLOR]``

I'm really not sure an array formula is warranted here. Anyway, to answer your question re zeroes:

=IF(COUNTIF(A6:A60,"A*"),SUMIF(A6:A60,"A*",E6:E60),"")

Regards

I'm really not sure an array formula is warranted here. Anyway, to answer your question re zeroes:

=IF(COUNTIF(A6:A60,"A*"),SUMIF(A6:A60,"A*",E6:E60),"")

Regards

Sorry for the runaround here, think I know what's going on now. There are 4 total possible bids on this sheet, bids A-D which will be in column A. In this example only a and b are used so for c & d the if false argument is tripped and no 0's are placed in cells (E63-AR66, 2nd pic). For any bids present on sheet (A & B here, pic 1 column A) the if statement is always true and sums each column even if all cells are blank and returns a 0 value. Is there a quick way to make all summed cells in E63:AR66 "" instead of 0 if there were no numbers in the respective columns to add?

[/IMG]

This formulas are in rows 63-66 now

Code:
``````=IF(COUNTIF(\$A\$6:\$A\$60,"A*"),SUMIF(\$A\$6:\$A\$60,"A*",E\$6:E\$60),"")
=IF(COUNTIF(\$A\$6:\$A\$60,"B*"),SUMIF(\$A\$6:\$A\$60,"B*",E\$6:E\$60),"")
=IF(COUNTIF(\$A\$6:\$A\$60,"C*"),SUMIF(\$A\$6:\$A\$60,"B*",E\$6:E\$60),"")
=IF(COUNTIF(\$A\$6:\$A\$60,"D*"),SUMIF(\$A\$6:\$A\$60,"B*",E\$6:E\$60),"")``````

[/IMG]

Thanks, Jordan

I'm overthinking it again, just found this

Use a number format to hide zero values in selected cells

Follow this procedure to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the general number format.

• Select the cells that contain the zero (0) values that you want to hide.
• On the Format menu, click Cells, and then click the Number tab.
• In the Category list, click Custom.
• In the Type box, type 0;-0;;@

Thanks for the help, those formulas worked great

Replies
6
Views
554
Replies
1
Views
370
Replies
3
Views
176
Replies
2
Views
361
Replies
11
Views
3K

1,217,754
Messages
6,138,418
Members
450,135
Latest member
Tabako1960

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back