Hi,
So I have a formula which contains a countifs that looks like this:
=COUNTIFS($AZ:$AZ,$AZ15,$AM:$AM,ABS($AM15),$AD:$AD,$AD15)
However, I want the formula to count values when ABS($AM:$AM)=ABS($AM15) but I get an error if I try to put ABS($AM:$AM) as my range. Is there anyway to set my range as the absolute value of Column?
I have also tried ABS($AM15:$AM1000).
Thanks,
Justin
Try entering it as an ARRAY formula...
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Unfortunelly an Array formula did not work either.
OK can you upload a small sample of what you are working with, and show what you want?
What are the properties/requirements on column AM? My first thought is to nest the current function/value in AM inside of the ABS() function, then proceed with the normal COUNTIFS() function.
In AM20: =ABS(current value or function)
In COUNTIFS() =COUNTIFS($AZ:$AZ,$AZ15,$AM:$AM,$AM15,$AD:$AD,$AD15)
If you cannot change the entries in column AM, add a helper column somewhere BB20 =ABS(AM20) copied as necessary, then refer to this helper column in the COUNTIFS() function.
Originally Posted by shg
This is just a simplified version and the formula in column C is only a part of a larger formula so I am hoping to keep it condensed but basically the values in Column C should be 3 because I want the countifs to be looking for the absolute value of column B.
use sumproduct
=SUMPRODUCT(--(ABS($B$1:$B$5000)=ABS(B1)))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Does this work?
Formula:
Please Login or Register to view this content.
This is an array formula & must be set by htting CTRL + SHIFT + ENTER
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Or in your cut-down version (also an array)
=SUM(IF(ABS($B:$B)=10,1))
The spreadsheet I will be using this formula in has thousands of rows, and I need it to count not just 10 but whatever value is in Column B for that row.
yes, so try the formula in post 8. Does it work (it was designed to meet your specin post #1.
Hi there,
Am I able to use the ABS function on this formula somehow?
=SUM(COUNTIFS('Sheet1'!A$2:A$191,{"0*","1*","2*","3*","4*","5*","6*","7*","8*","9*"},'Sheet1'!E$2:E$191,">"&F8))
I tried the following but it is not working:
=SUM(COUNTIFS('Sheet1'!A$2:A$191,{"0*","1*","2*","3*","4*","5*","6*","7*","8*","9*"},ABS('Sheet1'!E$2:E$191),">"&F8))
Is there something I am missing here? Tried all sorts of strategies around it and I am not able to do it. Please help.
lease start your own thread. Also, attach a sample file.
Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).
1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!
2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
3. Make sure that your desired solution is also shown (mock up the results manually).
4. Try to avoid using merged cells. They cause lots of problems!
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Maybe this:
=SUM(COUNTIFS(Sheet1!A$2:A$191,{"0*","1*","2*","3*","4*","5*","6*","7*","8*","9*"},Sheet1!E$2:E$191,{">";"<-"}&F8))
Rory
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks