Hell everyone,
I want to count the cells of column, the condition are
1. column Number is even. such as Column D,F,H...
2. the value if cell is greater then zer0.
How to write the formula , I have no idea.
Thanks
Hell everyone,
I want to count the cells of column, the condition are
1. column Number is even. such as Column D,F,H...
2. the value if cell is greater then zer0.
How to write the formula , I have no idea.
Thanks
=SUMPRODUCT((MOD(COLUMN(D2:K20),2)=0)*(D2:K20))
adjust the range to suit
--
HTH
RP
(remove nothere from the email address if mailing direct)
"whygh" <[email protected]> wrote in
message news:[email protected]...
>
> Hell everyone,
>
> I want to count the cells of column, the condition are
> 1. column Number is even. such as Column D,F,H...
> 2. the value if cell is greater then zer0.
>
> How to write the formula , I have no idea.
>
> Thanks
>
>
> --
> whygh
> ------------------------------------------------------------------------
> whygh's Profile:
http://www.excelforum.com/member.php...o&userid=29679
> View this thread: http://www.excelforum.com/showthread...hreadid=493965
>
This is for row 2...
=SUMPRODUCT((MOD(COLUMN(2:2), 2)=0)*(2:2>0))
--
HTH...
Jim Thomlinson
"whygh" wrote:
>
> Hell everyone,
>
> I want to count the cells of column, the condition are
> 1. column Number is even. such as Column D,F,H...
> 2. the value if cell is greater then zer0.
>
> How to write the formula , I have no idea.
>
> Thanks
>
>
> --
> whygh
> ------------------------------------------------------------------------
> whygh's Profile: http://www.excelforum.com/member.php...o&userid=29679
> View this thread: http://www.excelforum.com/showthread...hreadid=493965
>
>
Not to pick Bob but yours Sums. I think whygh was looking for a count. I know
that because you showed me how to do this... :-)
--
HTH...
Jim Thomlinson
"Bob Phillips" wrote:
> =SUMPRODUCT((MOD(COLUMN(D2:K20),2)=0)*(D2:K20))
>
> adjust the range to suit
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "whygh" <[email protected]> wrote in
> message news:[email protected]...
> >
> > Hell everyone,
> >
> > I want to count the cells of column, the condition are
> > 1. column Number is even. such as Column D,F,H...
> > 2. the value if cell is greater then zer0.
> >
> > How to write the formula , I have no idea.
> >
> > Thanks
> >
> >
> > --
> > whygh
> > ------------------------------------------------------------------------
> > whygh's Profile:
> http://www.excelforum.com/member.php...o&userid=29679
> > View this thread: http://www.excelforum.com/showthread...hreadid=493965
> >
>
>
>
Thank you guys!
I tried
"=SUMPRODUCT((MOD(COLUMN(2:2), 2)=0)*(2:2>0))"
it works.
AS to
"=SUMPRODUCT((MOD(COLUMN(D2:K20),2)=0)*(D2:K20))" ,
It is the sum of the even columns and values greater than zero. I also use it .
Vivian
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks