# 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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### XOR LX

##### Well-known Member
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

#### azumi

##### Well-known Member
=SUM(IF(LEFT(A6:A60,1)="A",E6:E60))

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

#### cyrilbrd

##### Well-known Member
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 ...

#### jedwardo

##### Board Regular

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:

#### jedwardo

##### Board Regular
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]``

#### XOR LX

##### Well-known Member

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

#### jedwardo

##### Board Regular
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]http://i62.tinypic.com/23r7msj.jpg[/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]http://i60.tinypic.com/904r2h.png[/IMG]

Thanks, Jordan

#### jedwardo

##### Board Regular
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
3
Views
36
Replies
4
Views
72
Replies
1
Views
33
Replies
4
Views
110
Replies
2
Views
137