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

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

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 thisHow-To.

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

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

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

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 3you 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.

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...)

Once more into the breach, dear friends, once moreTry this:

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

MIKE

Thanks for everyone's help!! I got it

Ask Your Question

Weekly Poll