| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
absolute function - range
I am trying to count cells in selected range that are above 50 and below -50.
How can I do this without creating another row with absolute values. I tried this but it doesn't work =countif(abs(u2:u2000),"50"). |
|
#2
|
|||
|
|||
|
absolute function - range
Try this:
=countif(u2:u2000,"50")+countif(u2:u2000,"<-50") "Aleks" wrote: I am trying to count cells in selected range that are above 50 and below -50. How can I do this without creating another row with absolute values. I tried this but it doesn't work =countif(abs(u2:u2000),"50"). |
|
#3
|
|||
|
|||
|
absolute function - range
Hi there Aleks,
You can use the following ... =SUMPRODUCT(--(ABS(U2:U2000)50)) HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... I am trying to count cells in selected range that are above 50 and below -50. How can I do this without creating another row with absolute values. I tried this but it doesn't work =countif(abs(u2:u2000),"50"). |
|
#4
|
|||
|
|||
|
absolute function - range
Outstanding!!! Thank yoy!
How about counting cells in selected range that are btw 5 to 50 and (-5) to (-50)? "Zack Barresse" wrote: Hi there Aleks, You can use the following ... =SUMPRODUCT(--(ABS(U2:U2000)50)) HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... I am trying to count cells in selected range that are above 50 and below -50. How can I do this without creating another row with absolute values. I tried this but it doesn't work =countif(abs(u2:u2000),"50"). |
|
#5
|
|||
|
|||
|
absolute function - range
Possibly ..
=SUMPRODUCT(--(ABS(N2:N2000)5),--(ABS(N2:N2000)50)) Note that this will not count those equal to 50. HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... Outstanding!!! Thank yoy! How about counting cells in selected range that are btw 5 to 50 and (-5) to (-50)? "Zack Barresse" wrote: Hi there Aleks, You can use the following ... =SUMPRODUCT(--(ABS(U2:U2000)50)) HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... I am trying to count cells in selected range that are above 50 and below -50. How can I do this without creating another row with absolute values. I tried this but it doesn't work =countif(abs(u2:u2000),"50"). |
|
#6
|
|||
|
|||
|
absolute function - range
This is awesome! Zack, whoever you are, you're a GOD!
"Zack Barresse" wrote: Possibly .. =SUMPRODUCT(--(ABS(N2:N2000)5),--(ABS(N2:N2000)50)) Note that this will not count those equal to 50. HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... Outstanding!!! Thank yoy! How about counting cells in selected range that are btw 5 to 50 and (-5) to (-50)? "Zack Barresse" wrote: Hi there Aleks, You can use the following ... =SUMPRODUCT(--(ABS(U2:U2000)50)) HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... I am trying to count cells in selected range that are above 50 and below -50. How can I do this without creating another row with absolute values. I tried this but it doesn't work =countif(abs(u2:u2000),"50"). |
|
#7
|
|||
|
|||
|
absolute function - range
I've been trying to tell him that for a year. Here's Zack!
http://www.vbaexpress.com/forum/member.php?u=11 ************ Anne Troy www.OfficeArticles.com "Aleks" wrote in message ... This is awesome! Zack, whoever you are, you're a GOD! "Zack Barresse" wrote: Possibly .. =SUMPRODUCT(--(ABS(N2:N2000)5),--(ABS(N2:N2000)50)) Note that this will not count those equal to 50. HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... Outstanding!!! Thank yoy! How about counting cells in selected range that are btw 5 to 50 and (-5) to (-50)? "Zack Barresse" wrote: Hi there Aleks, You can use the following ... =SUMPRODUCT(--(ABS(U2:U2000)50)) HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... I am trying to count cells in selected range that are above 50 and below -50. How can I do this without creating another row with absolute values. I tried this but it doesn't work =countif(abs(u2:u2000),"50"). |
|
#8
|
|||
|
|||
|
absolute function - range
:)
so here is another one ... I still have the same list u2:u2000 and there are both positive and negative numbers. I need to get a sum of all number 50 and <-50. again, I bow to Zack. "Anne Troy" wrote: I've been trying to tell him that for a year. Here's Zack! http://www.vbaexpress.com/forum/member.php?u=11 ************ Anne Troy www.OfficeArticles.com "Aleks" wrote in message ... This is awesome! Zack, whoever you are, you're a GOD! "Zack Barresse" wrote: Possibly .. =SUMPRODUCT(--(ABS(N2:N2000)5),--(ABS(N2:N2000)50)) Note that this will not count those equal to 50. HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... Outstanding!!! Thank yoy! How about counting cells in selected range that are btw 5 to 50 and (-5) to (-50)? "Zack Barresse" wrote: Hi there Aleks, You can use the following ... =SUMPRODUCT(--(ABS(U2:U2000)50)) HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... I am trying to count cells in selected range that are above 50 and below -50. How can I do this without creating another row with absolute values. I tried this but it doesn't work =countif(abs(u2:u2000),"50"). |
|
#9
|
|||
|
|||
|
absolute function - range
Hi Aleks
Just add another range to Zack's original formula =SUMPRODUCT(--(ABS(U2:U2000)50),U2:U2000) Regards Roger Govier Aleks wrote: :) so here is another one ... I still have the same list u2:u2000 and there are both positive and negative numbers. I need to get a sum of all number 50 and <-50. again, I bow to Zack. "Anne Troy" wrote: I've been trying to tell him that for a year. Here's Zack! http://www.vbaexpress.com/forum/member.php?u=11 ************ Anne Troy www.OfficeArticles.com "Aleks" wrote in message ... This is awesome! Zack, whoever you are, you're a GOD! "Zack Barresse" wrote: Possibly .. =SUMPRODUCT(--(ABS(N2:N2000)5),--(ABS(N2:N2000)50)) Note that this will not count those equal to 50. HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... Outstanding!!! Thank yoy! How about counting cells in selected range that are btw 5 to 50 and (-5) to (-50)? "Zack Barresse" wrote: Hi there Aleks, You can use the following ... =SUMPRODUCT(--(ABS(U2:U2000)50)) HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... I am trying to count cells in selected range that are above 50 and below -50. How can I do this without creating another row with absolute values. I tried this but it doesn't work =countif(abs(u2:u2000),"50"). |
|
#10
|
|||
|
|||
|
absolute function - range
This is beautiful!!!
How about the same as below but sum up only values that have "yes" a2:a2000 "Roger Govier" wrote: Hi Aleks Just add another range to Zack's original formula =SUMPRODUCT(--(ABS(U2:U2000)50),U2:U2000) Regards Roger Govier Aleks wrote: :) so here is another one ... I still have the same list u2:u2000 and there are both positive and negative numbers. I need to get a sum of all number 50 and <-50. again, I bow to Zack. "Anne Troy" wrote: I've been trying to tell him that for a year. Here's Zack! http://www.vbaexpress.com/forum/member.php?u=11 ************ Anne Troy www.OfficeArticles.com "Aleks" wrote in message ... This is awesome! Zack, whoever you are, you're a GOD! "Zack Barresse" wrote: Possibly .. =SUMPRODUCT(--(ABS(N2:N2000)5),--(ABS(N2:N2000)50)) Note that this will not count those equal to 50. HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... Outstanding!!! Thank yoy! How about counting cells in selected range that are btw 5 to 50 and (-5) to (-50)? "Zack Barresse" wrote: Hi there Aleks, You can use the following ... =SUMPRODUCT(--(ABS(U2:U2000)50)) HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... I am trying to count cells in selected range that are above 50 and below -50. How can I do this without creating another row with absolute values. I tried this but it doesn't work =countif(abs(u2:u2000),"50"). |
|
#11
|
|||
|
|||
|
absolute function - range
Hi Aleks
Try =SUMPRODUCT(--(A2:A2000)="yes"),U2:U2000) You need to read up on how Sumproduct works, then you can do any of these tasks very easily. A good starting point is http://xldynamic.com/source/xld.SUMPRODUCT.html Regards Roger Govier Aleks wrote: This is beautiful!!! How about the same as below but sum up only values that have "yes" a2:a2000 "Roger Govier" wrote: Hi Aleks Just add another range to Zack's original formula =SUMPRODUCT(--(ABS(U2:U2000)50),U2:U2000) Regards Roger Govier Aleks wrote: :) so here is another one ... I still have the same list u2:u2000 and there are both positive and negative numbers. I need to get a sum of all number 50 and <-50. again, I bow to Zack. "Anne Troy" wrote: I've been trying to tell him that for a year. Here's Zack! http://www.vbaexpress.com/forum/member.php?u=11 ************ Anne Troy www.OfficeArticles.com "Aleks" wrote in message ... This is awesome! Zack, whoever you are, you're a GOD! "Zack Barresse" wrote: Possibly .. =SUMPRODUCT(--(ABS(N2:N2000)5),--(ABS(N2:N2000)50)) Note that this will not count those equal to 50. HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... Outstanding!!! Thank yoy! How about counting cells in selected range that are btw 5 to 50 and (-5) to (-50)? "Zack Barresse" wrote: Hi there Aleks, You can use the following ... =SUMPRODUCT(--(ABS(U2:U2000)50)) HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... I am trying to count cells in selected range that are above 50 and below -50. How can I do this without creating another row with absolute values. I tried this but it doesn't work =countif(abs(u2:u2000),"50"). |
|
#12
|
|||
|
|||
|
absolute function - range
Sorry, typo
that should be =SUMPRODUCT(--(A2:A2000="yes"),U2:U2000) Regards Roger Govier Roger Govier wrote: Hi Aleks Try =SUMPRODUCT(--(A2:A2000)="yes"),U2:U2000) You need to read up on how Sumproduct works, then you can do any of these tasks very easily. A good starting point is http://xldynamic.com/source/xld.SUMPRODUCT.html Regards Roger Govier Aleks wrote: This is beautiful!!! How about the same as below but sum up only values that have "yes" a2:a2000 "Roger Govier" wrote: Hi Aleks Just add another range to Zack's original formula =SUMPRODUCT(--(ABS(U2:U2000)50),U2:U2000) Regards Roger Govier Aleks wrote: :) so here is another one ... I still have the same list u2:u2000 and there are both positive and negative numbers. I need to get a sum of all number 50 and <-50. again, I bow to Zack. "Anne Troy" wrote: I've been trying to tell him that for a year. Here's Zack! http://www.vbaexpress.com/forum/member.php?u=11 ************ Anne Troy www.OfficeArticles.com "Aleks" wrote in message ... This is awesome! Zack, whoever you are, you're a GOD! "Zack Barresse" wrote: Possibly .. =SUMPRODUCT(--(ABS(N2:N2000)5),--(ABS(N2:N2000)50)) Note that this will not count those equal to 50. HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... Outstanding!!! Thank yoy! How about counting cells in selected range that are btw 5 to 50 and (-5) to (-50)? "Zack Barresse" wrote: Hi there Aleks, You can use the following ... =SUMPRODUCT(--(ABS(U2:U2000)50)) HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... I am trying to count cells in selected range that are above 50 and below -50. How can I do this without creating another row with absolute values. I tried this but it doesn't work =countif(abs(u2:u2000),"50"). |
|
#13
|
|||
|
|||
|
absolute function - range
Huge thanks! I do some homework :)
"Roger Govier" wrote: Sorry, typo that should be =SUMPRODUCT(--(A2:A2000="yes"),U2:U2000) Regards Roger Govier Roger Govier wrote: Hi Aleks Try =SUMPRODUCT(--(A2:A2000)="yes"),U2:U2000) You need to read up on how Sumproduct works, then you can do any of these tasks very easily. A good starting point is http://xldynamic.com/source/xld.SUMPRODUCT.html Regards Roger Govier Aleks wrote: This is beautiful!!! How about the same as below but sum up only values that have "yes" a2:a2000 "Roger Govier" wrote: Hi Aleks Just add another range to Zack's original formula =SUMPRODUCT(--(ABS(U2:U2000)50),U2:U2000) Regards Roger Govier Aleks wrote: :) so here is another one ... I still have the same list u2:u2000 and there are both positive and negative numbers. I need to get a sum of all number 50 and <-50. again, I bow to Zack. "Anne Troy" wrote: I've been trying to tell him that for a year. Here's Zack! http://www.vbaexpress.com/forum/member.php?u=11 ************ Anne Troy www.OfficeArticles.com "Aleks" wrote in message ... This is awesome! Zack, whoever you are, you're a GOD! "Zack Barresse" wrote: Possibly .. =SUMPRODUCT(--(ABS(N2:N2000)5),--(ABS(N2:N2000)50)) Note that this will not count those equal to 50. HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... Outstanding!!! Thank yoy! How about counting cells in selected range that are btw 5 to 50 and (-5) to (-50)? "Zack Barresse" wrote: Hi there Aleks, You can use the following ... =SUMPRODUCT(--(ABS(U2:U2000)50)) HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... I am trying to count cells in selected range that are above 50 and below -50. How can I do this without creating another row with absolute values. I tried this but it doesn't work =countif(abs(u2:u2000),"50"). |
|
#14
|
|||
|
|||
|
absolute function - range
ROFL!! Thanks for the accolades Anne. You always were (and still are) my
biggest supporter. ;) Aleks, I have a written paper that may help with some understanding to what's going on in these formulas. I like it best for the links it contains, but others have said they enjoyed it. It only nips the tip of the iceberg, but may help ... http://www.vbaexpress.com/forum/arti...ticle&artid=42 -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Anne Troy" wrote in message ... I've been trying to tell him that for a year. Here's Zack! http://www.vbaexpress.com/forum/member.php?u=11 ************ Anne Troy www.OfficeArticles.com "Aleks" wrote in message ... This is awesome! Zack, whoever you are, you're a GOD! "Zack Barresse" wrote: Possibly .. =SUMPRODUCT(--(ABS(N2:N2000)5),--(ABS(N2:N2000)50)) Note that this will not count those equal to 50. HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... Outstanding!!! Thank yoy! How about counting cells in selected range that are btw 5 to 50 and (-5) to (-50)? "Zack Barresse" wrote: Hi there Aleks, You can use the following ... =SUMPRODUCT(--(ABS(U2:U2000)50)) HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) To email, remove the NO SPAM. Please keep correspondence to the board, as to benefit others. "Aleks" wrote in message ... I am trying to count cells in selected range that are above 50 and below -50. How can I do this without creating another row with absolute values. I tried this but it doesn't work =countif(abs(u2:u2000),"50"). |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "COUNTU" function in Excel to count unique entries in a range | Excel Worksheet Functions | |||
| creating function (vba) with range arguments | Excel Worksheet Functions | |||
| Number range function | Excel Discussion (Misc queries) | |||
| Function that filters a list (Database) for criteria in a range a. | Excel Worksheet Functions | |||
| Formula to list unique values | Excel Worksheet Functions | |||
Linear Mode
