# count numbers greater than1 in column E if  December 16, 2010 at 13:12:00
Specs: Windows XP
 I need to count the numbers in column "E" if they are greater zero * the number of cells in column "L" greater than zero.Basically I need the multiply the numbers in E by the cells in L if L is not empty. Do i use sumif or sumproduct.. what is the forumla?? See More: count numbers greater than1 in column E if

#1 December 16, 2010 at 16:24:26

Report •

#2 December 17, 2010 at 05:31:25
 This did not work, the formula that mmcconaghy gave me just multiplies all the numbers. I want to count the numbers in column 'E' only if there is a number in column 'L'. If there is no number in L30 I do not want to count E30

Report •

#3 December 17, 2010 at 06:04:29
 I can see what Mike was confused...I was too.You said "I need to count the numbers in column "E" if they are greater zero * the number of cells in column "L" greater than zero."Note the asterisk. That means multiply.Based on your clarification, look up the COUNTIF function in Excel help.Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

Related Solutions

#4 December 17, 2010 at 07:13:25
 In your first post you wanted:Basically I need the multiply the numbers in E by the cells in L if L is not emptyUpon clarification you actually want:I want to count the numbers in column 'E' only if there is a number in column 'L'. If you have 2007, instead of the =COUNTIF() as suggested by DerbyDad03, try the =COUNTIFS() function:=COUNTIFS(E1:E5,">0",L1:L5,">0")MIKE

Report •

#5 December 17, 2010 at 07:58:42
 I have excel 2003, when i insert this formula, =COUNTIF(E2:E23,">0",L2:L23,">0")I do not get a result. If there is a # in column L, I want to count the values in column E

Report •

#6 December 17, 2010 at 08:28:26
 If you have 2007Since you don't, the =COUNTIFS(), note the trailing S, is not available to you.Try this =SUMPRODUCT() function.=SUMPRODUCT(--(\$A\$1:\$A\$5>0),--(\$C\$1:\$C\$5>0))MIKEhttp://www.skeptic.com/

Report •

#7 December 17, 2010 at 09:16:26
 Since we cannot see your worksheet from where we are sitting, you need to be very specific when posting.If you say "If there is a # in column L" we might interpret that to be the pound sign which would lead us in the wrong direction.From your previous posts, I assume that if you had this:``` E L 1 4 2 3 3 5 6 4 9 12 5 3 ```you would expect a result of 2.If that is correct, then the function that Mike offered will work for you, as long as you use the correct cell references.=SUMPRODUCT(--(\$E\$1:\$E\$5>0),--(\$L\$1:\$L\$5>0))This alternate syntax should also work:=SUMPRODUCT((\$E\$1:\$E\$5>0)*(\$L\$1:\$L\$5>0))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#8 December 17, 2010 at 09:28:28
 I should have used the table 1st. thanks.. but I would expect a result of 14. what would the formula be to get a result of 14... (5+9...)

Report •

#9 December 17, 2010 at 09:44:21
 Once more into the breach, dear friends, once moreTry this:=SUMIF(L2:L6,">0",E2:E6)MIKEhttp://www.skeptic.com/

Report •

#10 December 17, 2010 at 10:08:40
 Thanks for everyone's help!! I got it

Report • 