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

Report •

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

=sum(E1:E100)*sum(L1:L100)

MIKE

http://www.skeptic.com/


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 empty

Upon 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

http://www.skeptic.com/


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 2007
Since 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))

MIKE

http://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 more

Try this:

=SUMIF(L2:L6,">0",E2:E6)

MIKE

http://www.skeptic.com/


Report •

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

Report •

Ask Question