countif >0, specific cells

[Renee - California]

I need to count specific cells in a row. I had a similar formula to average
the same cells I am now trying to count:

=AVERAGE(IF((MOD(COLUMN(Q13:IM13),10)=7)*(Q13:IM13>0),Q13:IM13))

I tried tweaking this to count, but it's just not coming out with a valid
answer.

Any help would be appreciated.

Thanks!

Renee.

 

[CLR]

If you are just wanting to count the number of cells in the range Q13:IM:13
that contain value larger than zero.........

=COUNTIF(Q13:IM13,">0")

Vaya con Dios,
Chuck, CABGx3

 

[Renee - California]

No, that's the problem, it's not that straight forward. I want to count Q13,
AA13, AK13... all the way to IM13.

The formula I used before indicates the start and end column, addressing
every 10th column, etc. but I can't recreate it just to do a count now.
YIKES!!!

 

[Aladin Akyurek]

For averaging...

{=AVERAGE(IF((MOD(COLUMN(Q13:IM13)-COLUMN(Q13)+0,10)=0)*(Q13:IM13>0),Q13:IM13))}

For counting...

=SUMPRODUCT(--(MOD(COLUMN(Q13:IM13)-COLUMN(Q13)+0,10)=0),--(Q13:IM13>0),--ISNUMBER(Q13:IM13))

I need to count specific cells in a row. I had a similar formula to average
the same cells I am now trying to count:

=AVERAGE(IF((MOD(COLUMN(Q13:IM13),10)=7)*(Q13:IM13>0),Q13:IM13))

I tried tweaking this to count, but it's just not coming out with a valid
answer.

Any help would be appreciated.

Thanks!

Renee.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[Ken Wright]

Juts realised your text doesn't tie with your title, so if it;s every 10th
where >0 then try

=SUMPRODUCT(--(MOD(COLUMN($Q$13:$IM$13),10)=7),--($Q$13:$IM$13>0))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Conrad Carlberg]

Hi Renee,

I suspect you've tried this:

=COUNT(IF((MOD(COLUMN(Q13:IM13),10)=7)*(Q13:IM13>0),Q13:IM13))

array-entered, so if that's not working, might there be a non-numeric value
in one or more of the cells you're counting, such that COUNT would then
return an undercount? Specifically what formula are you using to get a
count, and what is it returning? An error value? An erroneous value?

 

[Renee - California]

Yes, I was using the same formula (except in this worksheet it was shifted
one to the left); and I was getting 0.

=COUNT(IF((MOD(COLUMN(R14:IN14),10)=7)*(R14:IN14>0),R14:IN14))

 

[Renee - California]

This is counting now, but it's returning a count of "1" only.

A little more insight on this project:

Each worksheet represents a territory.
Each territory can contain up to 24 customers;
The data collected for each customer is a list of all the items I have to
offer, the price that particular customer pays for the item, and the amount
of facings they have of each item. Not every customer picks up every item.

What I'm trying to count is how many customers in that territory actually
picked up any given item.

Any more ideas??? I really appreciate your help.

 

[Renee - California]

THANK YOU, THANK YOU - THIS IS THE TICKET!!!! WOOHOO!!!!

For averaging...

{=AVERAGE(IF((MOD(COLUMN(Q13:IM13)-COLUMN(Q13)+0,10)=0)*(Q13:IM13>0),Q13:IM13))}

For counting...

=SUMPRODUCT(--(MOD(COLUMN(Q13:IM13)-COLUMN(Q13)+0,10)=0),--(Q13:IM13>0),--ISNUMBER(Q13:IM13))

I need to count specific cells in a row. I had a similar formula to average
the same cells I am now trying to count:

=AVERAGE(IF((MOD(COLUMN(Q13:IM13),10)=7)*(Q13:IM13>0),Q13:IM13))

I tried tweaking this to count, but it's just not coming out with a valid
answer.

Any help would be appreciated.

Thanks!

Renee.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.