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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0
=SUM(IF(LEFT(A6:A60,1)="A",E6:E60))

Array Formula, when ENTER you need to press CTRL-SHIFT-ENTER button together
 
Upvote 0
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 ...
 
Upvote 0
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:
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top