Excel 2013 Formula: =sumif(left(

jedwardo

Board Regular
Joined
Aug 21, 2012
Messages
122
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
 

Some videos you may like

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
Joined
Jul 2, 2012
Messages
4,517
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
Joined
Jun 4, 2013
Messages
555
=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
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
Joined
Aug 21, 2012
Messages
122

ADVERTISEMENT

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
Joined
Aug 21, 2012
Messages
122
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
Joined
Jul 2, 2012
Messages
4,517

ADVERTISEMENT

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
Joined
Aug 21, 2012
Messages
122
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
Joined
Aug 21, 2012
Messages
122
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,791
Messages
5,524,900
Members
409,610
Latest member
db321

This Week's Hot Topics

Top