Simple count function

[berminator]

Hi, I want to do a very simple calculation in excel

I have a number of columns, that can be populated with a 0 or non-0 value. I want a summary field that says number of non-zero columns?

Any help would be great.

Joe

[Ruthki]

Try using

=COUNTIF(G4:M4,"<>0")

where g to m are the column you are lookin for zero values in.


Ruth

[berminator]

That was excellent Ruth, Thanks!

Now, I have another one!

Of the range of cells, say 20 columns, I want to sum the best 14 from the 20, in order of highest rank.

So say I have 20 columns , with values of 1->20. I want the sum of the top 14, which would be 20+19+18+…etc.

Any easy way to do this?

Joe

[Bob Phillips]

=SUMPRODUCT(LARGE(A1:T1,ROW(INDIRECT("1:14"))))

--
HTH

Bob Phillips

"berminator" <[email protected]> wrote
in message news:[email protected]...
>
> That was excellent Ruth, Thanks!
>
> Now, I have another one!
>
> Of the range of cells, say 20 columns, I want to sum the best 14 from
> the 20, in order of highest rank.
>
> So say I have 20 columns , with values of 1->20. I want the sum of the
> top 14, which would be 20+19+18+.etc.
>
> Any easy way to do this?
>
> Joe
>
>
> --
> berminator
> ------------------------------------------------------------------------
> berminator's Profile:
http://www.excelforum.com/member.php...o&userid=24833
> View this thread: http://www.excelforum.com/showthread...hreadid=383851
>

[berminator]

Thanks again, worked a treat!

[Bob Phillips]

=SUMPRODUCT(LARGE(A1:T1,ROW(INDIRECT("1:14"))))

--
HTH

Bob Phillips

"berminator" <[email protected]> wrote
in message news:[email protected]...
>
> That was excellent Ruth, Thanks!
>
> Now, I have another one!
>
> Of the range of cells, say 20 columns, I want to sum the best 14 from
> the 20, in order of highest rank.
>
> So say I have 20 columns , with values of 1->20. I want the sum of the
> top 14, which would be 20+19+18+.etc.
>
> Any easy way to do this?
>
> Joe
>
>
> --
> berminator
> ------------------------------------------------------------------------
> berminator's Profile:
http://www.excelforum.com/member.php...o&userid=24833
> View this thread: http://www.excelforum.com/showthread...hreadid=383851
>

[Bob Phillips]

=SUMPRODUCT(LARGE(A1:T1,ROW(INDIRECT("1:14"))))

--
HTH

Bob Phillips

"berminator" <[email protected]> wrote
in message news:[email protected]...
>
> That was excellent Ruth, Thanks!
>
> Now, I have another one!
>
> Of the range of cells, say 20 columns, I want to sum the best 14 from
> the 20, in order of highest rank.
>
> So say I have 20 columns , with values of 1->20. I want the sum of the
> top 14, which would be 20+19+18+.etc.
>
> Any easy way to do this?
>
> Joe
>
>
> --
> berminator
> ------------------------------------------------------------------------
> berminator's Profile:
http://www.excelforum.com/member.php...o&userid=24833
> View this thread: http://www.excelforum.com/showthread...hreadid=383851
>

[Bob Phillips]

=SUMPRODUCT(LARGE(A1:T1,ROW(INDIRECT("1:14"))))

--
HTH

Bob Phillips

"berminator" <[email protected]> wrote
in message news:[email protected]...
>
> That was excellent Ruth, Thanks!
>
> Now, I have another one!
>
> Of the range of cells, say 20 columns, I want to sum the best 14 from
> the 20, in order of highest rank.
>
> So say I have 20 columns , with values of 1->20. I want the sum of the
> top 14, which would be 20+19+18+.etc.
>
> Any easy way to do this?
>
> Joe
>
>
> --
> berminator
> ------------------------------------------------------------------------
> berminator's Profile:
http://www.excelforum.com/member.php...o&userid=24833
> View this thread: http://www.excelforum.com/showthread...hreadid=383851
>

[Bob Phillips]

=SUMPRODUCT(LARGE(A1:T1,ROW(INDIRECT("1:14"))))

--
HTH

Bob Phillips

"berminator" <[email protected]> wrote
in message news:[email protected]...
>
> That was excellent Ruth, Thanks!
>
> Now, I have another one!
>
> Of the range of cells, say 20 columns, I want to sum the best 14 from
> the 20, in order of highest rank.
>
> So say I have 20 columns , with values of 1->20. I want the sum of the
> top 14, which would be 20+19+18+.etc.
>
> Any easy way to do this?
>
> Joe
>
>
> --
> berminator
> ------------------------------------------------------------------------
> berminator's Profile:
http://www.excelforum.com/member.php...o&userid=24833
> View this thread: http://www.excelforum.com/showthread...hreadid=383851
>

[Bob Phillips]

=SUMPRODUCT(LARGE(A1:T1,ROW(INDIRECT("1:14"))))

--
HTH

Bob Phillips

"berminator" <[email protected]> wrote
in message news:[email protected]...
>
> That was excellent Ruth, Thanks!
>
> Now, I have another one!
>
> Of the range of cells, say 20 columns, I want to sum the best 14 from
> the 20, in order of highest rank.
>
> So say I have 20 columns , with values of 1->20. I want the sum of the
> top 14, which would be 20+19+18+.etc.
>
> Any easy way to do this?
>
> Joe
>
>
> --
> berminator
> ------------------------------------------------------------------------
> berminator's Profile:
http://www.excelforum.com/member.php...o&userid=24833
> View this thread: http://www.excelforum.com/showthread...hreadid=383851
>

[Bob Phillips]

=SUMPRODUCT(LARGE(A1:T1,ROW(INDIRECT("1:14"))))

--
HTH

Bob Phillips

"berminator" <[email protected]> wrote
in message news:[email protected]...
>
> That was excellent Ruth, Thanks!
>
> Now, I have another one!
>
> Of the range of cells, say 20 columns, I want to sum the best 14 from
> the 20, in order of highest rank.
>
> So say I have 20 columns , with values of 1->20. I want the sum of the
> top 14, which would be 20+19+18+.etc.
>
> Any easy way to do this?
>
> Joe
>
>
> --
> berminator
> ------------------------------------------------------------------------
> berminator's Profile:
http://www.excelforum.com/member.php...o&userid=24833
> View this thread: http://www.excelforum.com/showthread...hreadid=383851
>

[Bob Phillips]

=SUMPRODUCT(LARGE(A1:T1,ROW(INDIRECT("1:14"))))

--
HTH

Bob Phillips

"berminator" <[email protected]> wrote
in message news:[email protected]...
>
> That was excellent Ruth, Thanks!
>
> Now, I have another one!
>
> Of the range of cells, say 20 columns, I want to sum the best 14 from
> the 20, in order of highest rank.
>
> So say I have 20 columns , with values of 1->20. I want the sum of the
> top 14, which would be 20+19+18+.etc.
>
> Any easy way to do this?
>
> Joe
>
>
> --
> berminator
> ------------------------------------------------------------------------
> berminator's Profile:
http://www.excelforum.com/member.php...o&userid=24833
> View this thread: http://www.excelforum.com/showthread...hreadid=383851
>

[Bob Phillips]

=SUMPRODUCT(LARGE(A1:T1,ROW(INDIRECT("1:14"))))

--
HTH

Bob Phillips

"berminator" <[email protected]> wrote
in message news:[email protected]...
>
> That was excellent Ruth, Thanks!
>
> Now, I have another one!
>
> Of the range of cells, say 20 columns, I want to sum the best 14 from
> the 20, in order of highest rank.
>
> So say I have 20 columns , with values of 1->20. I want the sum of the
> top 14, which would be 20+19+18+.etc.
>
> Any easy way to do this?
>
> Joe
>
>
> --
> berminator
> ------------------------------------------------------------------------
> berminator's Profile:
http://www.excelforum.com/member.php...o&userid=24833
> View this thread: http://www.excelforum.com/showthread...hreadid=383851
>

[Bob Phillips]

=SUMPRODUCT(LARGE(A1:T1,ROW(INDIRECT("1:14"))))

--
HTH

Bob Phillips

"berminator" <[email protected]> wrote
in message news:[email protected]...
>
> That was excellent Ruth, Thanks!
>
> Now, I have another one!
>
> Of the range of cells, say 20 columns, I want to sum the best 14 from
> the 20, in order of highest rank.
>
> So say I have 20 columns , with values of 1->20. I want the sum of the
> top 14, which would be 20+19+18+.etc.
>
> Any easy way to do this?
>
> Joe
>
>
> --
> berminator
> ------------------------------------------------------------------------
> berminator's Profile:
http://www.excelforum.com/member.php...o&userid=24833
> View this thread: http://www.excelforum.com/showthread...hreadid=383851
>

[Bob Phillips]

=SUMPRODUCT(LARGE(A1:T1,ROW(INDIRECT("1:14"))))

--
HTH

Bob Phillips

"berminator" <[email protected]> wrote
in message news:[email protected]...
>
> That was excellent Ruth, Thanks!
>
> Now, I have another one!
>
> Of the range of cells, say 20 columns, I want to sum the best 14 from
> the 20, in order of highest rank.
>
> So say I have 20 columns , with values of 1->20. I want the sum of the
> top 14, which would be 20+19+18+.etc.
>
> Any easy way to do this?
>
> Joe
>
>
> --
> berminator
> ------------------------------------------------------------------------
> berminator's Profile:
http://www.excelforum.com/member.php...o&userid=24833
> View this thread: http://www.excelforum.com/showthread...hreadid=383851
>

[Bob Phillips]

=SUMPRODUCT(LARGE(A1:T1,ROW(INDIRECT("1:14"))))

--
HTH

Bob Phillips

"berminator" <[email protected]> wrote
in message news:[email protected]...
>
> That was excellent Ruth, Thanks!
>
> Now, I have another one!
>
> Of the range of cells, say 20 columns, I want to sum the best 14 from
> the 20, in order of highest rank.
>
> So say I have 20 columns , with values of 1->20. I want the sum of the
> top 14, which would be 20+19+18+.etc.
>
> Any easy way to do this?
>
> Joe
>
>
> --
> berminator
> ------------------------------------------------------------------------
> berminator's Profile:
http://www.excelforum.com/member.php...o&userid=24833
> View this thread: http://www.excelforum.com/showthread...hreadid=383851
>