| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
countif >0, specific cells
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. |
|
#2
|
|||
|
|||
|
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" wrote in message ... 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. |
|
#3
|
|||
|
|||
|
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!!! "CLR" wrote: 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" wrote in message ... 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. |
|
#4
|
|||
|
|||
|
For averaging...
{=AVERAGE(IF((MOD(COLUMN(Q13:IM13)-COLUMN(Q13)+0,10)=0)*(Q13:IM130),Q13:IM13))} For counting... =SUMPRODUCT(--(MOD(COLUMN(Q13:IM13)-COLUMN(Q13)+0,10)=0),--(Q13:IM130),--ISNUMBER(Q13:IM13)) Renee - California wrote: 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. |
|
#5
|
|||
|
|||
|
Just count you said - no criteria to fill other than being every "10th" Col
but starting with Q inclusive :- =SUMPRODUCT(--(MOD(COLUMN($Q$13:$IM$13),10)=7)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Renee - California" wrote in message ... 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!!! "CLR" wrote: 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" wrote in message ... 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. |
|
#6
|
|||
|
|||
|
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$130)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Renee - California" wrote in message ... 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!!! "CLR" wrote: 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" wrote in message ... 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. |
|
#7
|
|||
|
|||
|
Hi Renee,
I suspect you've tried this: =COUNT(IF((MOD(COLUMN(Q13:IM13),10)=7)*(Q13:IM130 ),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? -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 "Renee - California" wrote in message ... 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!!! "CLR" wrote: 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" wrote in message ... 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. |
|
#8
|
|||
|
|||
|
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:IN140 ),R14:IN14)) "Conrad Carlberg" wrote: Hi Renee, I suspect you've tried this: =COUNT(IF((MOD(COLUMN(Q13:IM13),10)=7)*(Q13:IM130 ),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? -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 "Renee - California" wrote in message ... 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!!! "CLR" wrote: 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" wrote in message ... 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. |
|
#9
|
|||
|
|||
|
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. "Ken Wright" wrote: 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$130)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "Renee - California" wrote in message ... 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!!! "CLR" wrote: 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" wrote in message ... 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. |
|
#10
|
|||
|
|||
|
THANK YOU, THANK YOU - THIS IS THE TICKET!!!! WOOHOO!!!!
"Aladin Akyurek" wrote: For averaging... {=AVERAGE(IF((MOD(COLUMN(Q13:IM13)-COLUMN(Q13)+0,10)=0)*(Q13:IM130),Q13:IM13))} For counting... =SUMPRODUCT(--(MOD(COLUMN(Q13:IM13)-COLUMN(Q13)+0,10)=0),--(Q13:IM130),--ISNUMBER(Q13:IM13)) Renee - California wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I count cells beginning with specific letter? | New Users to Excel | |||
| keep specific cells from being changed. | Excel Discussion (Misc queries) | |||
| Can specific cells on a worksheet be hidden & password protected? | Excel Worksheet Functions | |||
| CountIF cells are not empty | Excel Discussion (Misc queries) | |||
| How do I retrieve data (specific cells) from multiple worksheets on a shared drive | Excel Worksheet Functions | |||
Linear Mode
