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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

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,085
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?

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),"")


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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,827
Messages
5,855,878
Members
431,771
Latest member
CoryMelth

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top