COUNTIFS with OR

[momi]

Hi, have you ever met with this king of problem? I am using a countifs function, but in one "criteria" I need to built in "OR" function, I am trying to put to the "criteria" field: OR($F$22,$G$22), but it does not work. What I want to achieve is, that my criteria range is compared with two criteria in cell $F$22 and in cell $G$22. thanks. Michal

 

[Luke M]

Just add two COUNTIF(s) functions together. E.g.,

=COUNTIF(A:A,F22)+COUNTIF(A:A,G22)

 

[momi]

Yes, you are exactly right, but right now my counifs has 1,5 line (more criterias), If I would need 6 parameters in OR function, than my formula would have 9 lines, I wanted to avoid this - if possible of course, if not, then I will use solution - you mentioned. In any case, thank you for help. Michal

 

[Luke M]

Other method would be to use SUMPRODUCT, like this:

=SUMPRODUCT(--(A2:A10=Criteria1),--(A2:A10=Criteria2),--(A2:A10=Criteria3),--(A2:A10=Criteria4))

 

[PSG]

Luke I am trying to start a new topic but not being able to do so.


can you help me find the error in this code.


Sub FillInternetForm()

Dim objIE As Object

Set objIE = CreateObject("InternetExplorer.Application")


objIE.Navigate "http://www.cvent.com/RFP/NewSupplierRequest.aspx?

vtt=1"

objIE.Visible = True


Do While objIE.busy

While objIE.busy

DoEvents

Wend

Loop


objIE.Document.getElementById("txtSupplierName").Value = "PRAKASH"


Do While objIE.busy

While objIE.busy

DoEvents

Wend

Loop


End Sub


this code is giving me run time error with message as "Method

'Document' of object 'IWebBrowser2' failed.

 

[PSG]

Nice Solution Luke!

 

[jeremymjp]

Luke, what's the function of the double negatives in front of the arrays?

 

[Luke M]

jeremymjp,


It's used to force the function to convert boolean values into numerical ones. A single negative would change this array:

TRUE,FALSE,FALSE,TRUE

into this:

-1,0,0,-1

So, a double negative gives this:

1,0,0,1


The SUMPRODUCT can then easily multiply them all together. Alternatively, you can write:

=SUMPRODUCT((A2:A10=Criteria1)*(A2:A10=Criteria2)*(A2:A10=Criteria3)*(A2:A10=Criteria4))


The * does a similar job of forcing the function to multiply the boolean arrays, which will force them to be converted to numbers. I just personally like using the double negative notation as I find it easier to read/debug.

 

[jeremymjp]

That's cool. Would the same value be produced if you replaced "Sumproduct" in the alternative method with the formula "Sum"?


As in:

=SUM((A2:A10=Criteria1)*(A2:A10=Criteria2)*(A2:A10=Criteria3)*(A2:A10=Criteria4))


Good to know the double negative trick.

 

[Luke M]

Yep, assuming you made it an array formula.

 

[xcruc1at3r]

what is the basic meaning of array formula ?

 

[asa]

From Microsoft:

A quick introduction to arrays and array formulas


If you've done even a little programming, you've probably run across the term array. For our purposes, an array is a collection of items. In Excel, those items can reside in a single row (called a one-dimensional horizontal array), a column (a one-dimensional vertical array), or multiple rows and columns (a two-dimensional array). You cannot create three-dimensional arrays or array formulas in Excel.


An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and use the array formula to calculate a column or row of subtotals. You can also place an array formula in a single cell and then calculate a single amount. An array formula that resides in multiple cells is called a multi-cell formula, and an array formula that resides in a single cell is called a single-cell formula.

Here's the article: Guildines and Examples of array formulas. It goes into some depth about array formulas.


In short, an array means multiple values. A simple range A1:A3 is an array. An array formula can do operations on multiple arrays without aggregating (SUM, AVERAGE, etc.) first. Then in the end, if you want the result in one cell (rather than multiple results in an array of cells), you can wrap an agrregating function around your result array to get a single result.

Given:

[pre]

/// A__________ B__________ C__________
[1] Item Color       Price    Quantity
[2] Red              $5.00           9
[3] Blue             $4.00           8
[4] Blue             $4.50          10
[5] Red              $7.00           3
The array formula

[pre][code]{=IF($A$2:$A$5="Red",$B$2:$B$5*$C$2:$C$5,"")}
would give you the net (sales) amount for each red item, in the following array:

[pre][code]$45.00
[i] [/i]

[pre][code]$21.00[/pre]
For the total sales in red items, add SUM() around it:

[code]{=SUM(IF($A$2:$A$5="Red",$B$2:$B$5*$C$2:$C$5,""))}[/pre]
Result: [code]$66.00


Or for the average transaction amount in red items, use AVERAGE:

[code]{=AVERAGE(IF($A$2:$A$5="Red",$B$2:$B$5*$C$2:$C$5,""))}[/pre]
Result: [code]$33.00

Basically, when you use arrays in places where you would ordinarily just use a single value in a calculation, and enter it as an array formula, Excel doe the mathematical or other operation on each cell/value in the the first array with the corresponding value in each other array, according to position. In my example {=IF($A$2:$A$5="Red",$B$2:$B$5*$C$2:$C$5)}[/code], Excel would calculate the result array as follows:

=IF($A$2="Red",$B$2*$C$2,"&#34
=IF($A$3="Red",$B$3*$C$3,"&#34
=IF($A$4="Red",$B$4*$C$4,"&#34
=IF($A$5="Red",$B$5*$C$5,"&#34[/code][/pre]
You can then see that SUM() or AVERAGE(), etc. would operate on the results of these calculations.


There are some not so obvious tricks you often see used with array formulas, like the --(boolean test)[/code] Luke used to turn TRUE/FALSE into 1/0, and using ROW()[/code] and COLUMN()[/code] functions to create dynamic or large arrays of constant, sequential values. or TRANSPOSE()[/code] to switch vertical for horizontal in your array result. SUMPRODUCT()[/code] is essentially a slightly limited array formula evaluator, but it's popular because it can perform certain calculations more quickly, it can be used in places where array formulas aren't allowed (data validation rules, etc.), and because people are often nervous about array formulas or don't understand how to enter them, and SUMPRODUCT is more approachable.


Asa

 

[asa]

Actually, Luke, aren't these both:

[pre]

=SUMPRODUCT(--(A2:A10=Criteria1),--(A2:A10=Criteria2),--(A2:A10=Criteria3),--(A2:A10=Criteria4))
=SUMPRODUCT((A2:A10=Criteria1)*(A2:A10=Criteria2)*(A2:A10=Criteria3)*(A2:A10=Criteria4))
AND truth tests? If any one of them fails, (=0) the whole product will be zeroed out; each must be True/1 for a result of 1.


I guess for OR, you'd use either of these:

[pre][code]=SUMPRODUCT(--($A$2:$A$10=Criteria1)-($A$2:$A$10=Criteria2)-($A$2:$A$10=Criteria3)-($A$2:$A$10=Criteria4))>0
{=SUM(--($A$2:$A$10=Criteria1)-($A$2:$A$10=Criteria2)-($A$2:$A$10=Criteria3)-($A$2:$A$10=Criteria4))>0}

[/pre]
If your Criteria are all in a range of cells, such as F22:I22, I think you could simplify it to:

=SUMPRODUCT(--($A$2:$A$10=$F22:$I22))>0
{=SUM(--($A$2:$A$10=$F22:$I22))>0}[/code][/pre]
Asa

 

[asa]

To mix and and or, you can mix the techniques..


oops, edited I better try it before I give a combo formula.. my suggestion didn't look right

 

[asa]

OK, Here we go.

The AND test for criteria in sequential columns. Either of:

[pre]

=SUMPRODUCT(--($A$2:$A$10=$J22:$M22))=4
{=SUM(--($A$2:$A$10=$J22:$M22))=4}
(Array formula in braces)

Note the =4 in there - it needs to = the number of criteria (J, K, L, M columns = 4).

You could replace the =4 with [code]COLUMNS($J22:$M22) if preferred.  Might help make the formula more stable if you are using named ranges for criteria.. i.e. named range for the criteria columns, and (assuming you want to nab the criteria from the current row), using the intersect operator (space) with your named range and a relative reference to the current row should retrieve the applicable criteria:

[pre][code]=SUMPRODUCT(--(data_range=criteria_range 22:22))=COLUMNS(criteria_range)
{=SUM(--(data_range=criteria_range 22:22))=COLUMNS(criteria_range)}

To combine the OR and AND groups of criteria, just use AND():

=AND(SUMPRODUCT(--($A$2:$A$10=$F22:$I22))>0,SUMPRODUCT(--($A$2:$A$10=$J22:$M22))=4)
{=AND(SUM(--($A$2:$A$10=$F22:$I22))>0,SUM(--($A$2:$A$10=$J22:$M22))=4)}[/code][/pre]
Named ranges versions:

=AND(SUMPRODUCT(--(data_range=OR_criteria_range 22:22))>0,SUMPRODUCT(--(data_range=AND_criteria_range 22:22))=COLUMNS(AND_criteria_range))
{=AND(SUM(--(data_range=OR_criteria_range 22:22))>0,SUM(--(data_range=AND_criteria_range 22:22))=COLUMNS(AND_criteria_range))}[/code][/pre]
Asa

 

[asa]

In my first comment with what I believed were corrected general purpose "OR" calculations:

I guess for OR, you'd use either of these:


=SUMPRODUCT(--($A$2:$A$10=Criteria1)-($A$2:$A$10=Criteria2)-($A$2:$A$10=Criteria3)-($A$2:$A$10=Criteria4))>0

{=SUM(--($A$2:$A$10=Criteria1)-($A$2:$A$10=Criteria2)-($A$2:$A$10=Criteria3)-($A$2:$A$10=Criteria4))>0}


If your Criteria are all in a range of cells, such as F22:I22, I think you could simplify it to:


=SUMPRODUCT(--($A$2:$A$10=$F22:$I22))>0

{=SUM(--($A$2:$A$10=$F22:$I22))>0}

The version for ranges of criteria is correct, but the long version with criteria specified individually is incorrect. The - signs between tests need to be doubled, or else just eliminate all - signs and use plus between criteria (will work in this instance). Not sure what I was thinking..


Correct version, and more efficient than --:

[pre]

=SUMPRODUCT(($A$2:$A$10=Criteria1)+($A$2:$A$10=Criteria2)+($A$2:$A$10=Criteria3)+($A$2:$A$10=Criteria4))>0
{=SUM(($A$2:$A$10=Criteria1)+($A$2:$A$10=Criteria2)+($A$2:$A$10=Criteria3)+($A$2:$A$10=Criteria4))>0}

[/pre]
Braces indicate array formula entry (ctrl shift enter).

 

[xcruc1at3r]

well asa @

If your Criteria are all in a range of cells, such as F22:I22, I think you could simplify it to:

=SUMPRODUCT(--($A$2:$A$10=$F22:$I22))>0

{=SUM(--($A$2:$A$10=$F22:$I22))>0}

U have added two formula's together in above mentioned formula right ! sumproduct & Sum rite? well let me try to explain the formula as you placed...if am wrong so do correct me .... -- or * is used to interlink the two criteria togehter is say if The A2 : A10(Data range_row) = is equivalent to F22:I22 (range_column) or greater then 0 so only then Sum A2 : A10(Data range_row) = is equivalent to F22:I22 (range_column) or greater then 0

rite!!... well but !!! what will be the result ? :S will it sum the values in a1 to a10 of f22 to I22 ????

 

[xcruc1at3r]

well i need to clearly understand till this part after that i'd move onwards to understand it perfectly...so that i can understand how to join two formula's or more formulas togehter...

 

[asa]

Sorry those are two alternative methods:

(1) sumproduct

(2) array formula


I did that with all my examples. Sorry if I wasn't clear.


Both work the same and juts as well.


For the array formula, don't type the curly braces on the outside of the formula. But do hit Ctrl-Shift-Enter instead of Enter after typing it in to confirm it as an array formula.


Your choice.

 

[asa]

-- converts true to 1 and false to 0 (forces conversion to a number)


in certain calculations, OR() and AND() cannot be used, so one has to use arithmetic operations to achieve the same logic.


multiplication, then test for =1 : AND LOGIC

addition, then test for >0 : OR LOGIC

addition, then test for =#_of_criteria : AND LOGIC


will review the rest of your question later

 

[asa]

For a detailed explanation of how this works, check Chandoo's article "Advanced Sumproduct Queries".


Particularly read the "Scenario 4: Sum values within a 2D Range matching multiple ordered criteria" section.


Explained are what SUMPRODUCT does, and what adding and multiplying do. There is a sample workbook to go along with the article.


-- isn't used in that article, because it is only required in certain circumstances to force conversion to 1 and 0; the conversion to 1 and 0 from true and false can happen in other ways (I'll be vague unless you want more details).


I will just add that

SUMPRODUCT (blablabla) is equivalent to an array formula of the form [code]SUM(blablabla), but with no need for ctrl-shift-enter when typing the formula.


SUMPRODUCT can also take multiple arguments, though: [code]SUMPRODUCT(blablabla,blablabla,blablabla...)

which actually would be equivalent in an array formula to: SUM((blablabla)*(blablabla)*(blablabla))[/code] .. this is the reason for the word "product" in "SUMPRODUCT".


You might want to just ignore array formulas for now and use SUMPRODUCT. Usually the only time you have to use an array formula instead of SUMPRODUCT is if you want to do something beside summing the result.


.

Also good reading from Chandoo: "What is Excel SUMPRODUCT formula and how to use it?" for a basic introduction.


.

In the case of the example formula from me that you quoted:

well let me try to explain the formula as you placed...if am wrong so do correct me .... -- or * is used to interlink the two criteria togehter is say if The A2 : A10(Data range_row) = is equivalent to F22:I22 (range_column) or greater then 0 so only then Sum A2 : A10(Data range_row) = is equivalent to F22:I22 (range_column) or greater then 0

rite!!... well but !!! what will be the result ? :S will it sum the values in a1 to a10 of f22 to I22 ????

Not exactly---or else I didn't quite follow you .

.


I'll "illustrate" it for you.


The following formula:

=SUMPRODUCT(--($A$2:$A$10=$F22:$I22))>0[/code]

is evaluated in many steps by Excel, in an array (2-dimensional matrix) calculation, as follows:

stage step calculation___________________ result_________________________
1A 1. $A$2=$F22 True or False
2. --(True or False) 1 or 0
1B 1. $A$2=$G22 True or False
2. --(True or False) 1 or 0
1C 1. $A$2=$H22 True or False
2. --(True or False) 1 or 0
1D 1. $A$2=$I22 True or False
2. --(True or False) 1 or 0

2A 1. $A$3=$F22 True or False
2. --(True or False) 1 or 0
2B 1. $A$3=$G22 True or False
2. --(True or False) 1 or 0
2C 1. $A$3=$H22 True or False
2. --(True or False) 1 or 0
2D 1. $A$3=$I22 True or False
2. --(True or False) 1 or 0

3A 1. $A$4=$F22 True or False
2. --(True or False) 1 or 0
3B 1. $A$4=$G22 True or False
2. --(True or False) 1 or 0
3C 1. $A$4=$H22 True or False
2. --(True or False) 1 or 0
3D 1. $A$4=$I22 True or False
2. --(True or False) 1 or 0

...etcetera, all the way through......

9A 1. $A$10=$F22 True or False
2. --(True or False) 1 or 0
9B 1. $A$10=$G22 True or False
2. --(True or False) 1 or 0
9C 1. $A$10=$H22 True or False
2. --(True or False) 1 or 0
9D 1. $A$10=$I22 True or False
2. --(True or False) 1 or 0

and then,

10 SUM(1A:9D step 2 results) a number between 0 and 36

11 (a number between 0 and 36)>0 True or False

This results in an OR logic because each match between a criteria and your data results in a 1, then all the 1s are added up, and if the result is >0 then 1 or more matches had been found.


Chandoo's article has illustrations to show what the matrices look like to Excel when evaluating this type of formula.


To reiterate, -- forces 1s and 0s. Adding 1s and 0s gives you a count of 1s. If more than 0, you had at least 1 "1" ("OR" logic).


As to the * multiplication operator, it results in "AND" logic because 0 times anything = 0, and 1 times 1 = 1. So if ANY 0s are present in a string of numbers multiplied, the result will always be 0. Check for =1 and you will get True/False indicating that all conditions were met in all cases.


.

I tend to misspeak sometimes, and I realize after illustrating this example, that my previous statement of another way to do an AND test is incorrect. I said:

addition, then test for =#_of_criteria : AND LOGIC

Obviously if you have 4 criteria cells and 9 cells to compare to, the result would be 4*9 1s and 0s ----- up to 36 when added together. So for all criteria to be met for all 9 tested cells, you would have to check for =36, or =4*9. I had some circumstance recently requiring this logic instead of the usual multiplication = 1 test... I forget why, though.


Hope this helps.


Asa


EDIT:

hahaha.. the "recent circumstance" was earlier in this thread when I gave you the short calculation for an AND test on a vertical range of data and a horizontal range of criteria...


I apologize for the mistakes. I will add another post in a moment correcting for my error and give you a new AND formula and a new combo formula with "AND" and "OR".

 

[asa]

Corrected AND test for a vertical range of data and a horizontal range of criteria.

I did not find a good SUMPRODUCT solution, only an array formula solution.


All formulas mentioned in this post need Ctrl-Shift-Enter when editing/entering the formula:

---


This will result in TRUE if every data cell matches at least one of the criteria cells:

=AND(NOT(ISNA(MATCH($A$2:$A$10,$J22:$M22,0))))


Another interpretation of and;

This will result in TRUE if every criteria cell matches at least one of the data cells:

[code]=AND(NOT(ISNA(MATCH($J22:$M22,$A$2:$A$10,0))))


A third interpretation of and (not very likely);

This will result in TRUE if every data cell matches ALL of the criteria (all the data and all the criteria would in fact be the same single value repeated):

[code]=AND($A$2:$A$10=$J22:$M22)


Now, the MATCH() function has an interesting feature.  It supports wildcard characters; however, it only supports them in it's first parameter.  The second interpretation of "and" has the criteria in the first parameter, so if that is the relevant interpretation, you can use * and ? in your criteria as wildcards!


Unfortunately, if it's the first interpretation of "and", as I suspect it is, I see no obvious way to leverage the wildcard capability if you want the wildcards in the criteria (as opposed to in the data).


... Which highlights a potential issue:  if your data might contain * or ? characters , they will be interpreted as wildcards and could match criteria that you wouldn't expect them to.  This issue can be worked around, but I hate to make the formula longer or slower unless required.  Also note that MATCH is not case sensitive when comparing text.


~ ~ ~ ~ ~


I can (eventually) solve problems in Excel, but I don't have experience enough, at least in recent years, to have ready-made solutions much of the time.  I found this particular formula a real challenge to come up with.  MATCH was far from the first thing I tried -- it wasn't really in my toolbox, although I knew about it.  I tried many things that SHOULD work, but didn't -- apparently bumping against Excel's limitations.


Some of the formulas that should, I believe, in theory, work, but don't (for the curious) (all for the first interpretation of and):


[code]=AND(NOT(ISNA(HLOOKUP($A$2:$A$10,$J22:$M22,1,FALSE))))


[code]=AND(NOT(ISNA(INDEX($J22:$M22,1,MATCH($A$2:$A$10,$J22:$M22,0)))))


=AND(SUM(INDEX(--($A$2:$A$10=$J22:$M22),ROW($A$2:$A$10)-ROW(INDEX($A$2:$A$10,1,1))+1))>0)

The latter of these three is anything but transparent (I'm slightly amazed I got multiple SUM[/code]s out of SUM()[/code]). Of course: none of them work anyway. Replacing AND()[/code] with PRODUCT(--(formula))[/code] fails the same way.


Remove the AND() wrapper and you get a 1 dimensional vertical array containing True or False indicators of whether each row in the dataset met 1 or more criteria. Thus I say my above formulas should work.


An Offset/Match version DID work. But then I thought, I don't want to use Offset --- it's a volatile (read: slow) function. And then I realized -- duh -- we don't need to lookup the exact text of the matching criteria. Match already alone gives more info than required (the relative position of the matching criteria). We only need to know if there was a match. And finally, I was happy.


Learned: it appears that Index() and Hlookup() break array formulas in that the resulting array, although it can have arithmetic processing (leading -- tested), cannot be handled by aggregation functions. I assume the same problem with Vlookup and Lookup, although untested. And, happily, Match and Offset both leave array formulas healthy, and the unhealthy array formulas if left as multi-cell formulas can be aggregated from another cell.

 

[asa]

Bringing it together: the working formulas I've proposed so far...


Testing a range of data (e.g. $A$2:$A$10)

for matching a range of criteria ($F22:$I22)

--------------------------------------------------------

OR test:

=SUMPRODUCT(--($A$2:$A$10=$F22:$I22))>0

[b]AND test:[b] [b]{ [code]=AND(NOT(ISNA(MATCH($A$2:$A$10,$F22:$I22,0)))) [b]}


The AND test needs array entry (ctrl-shift-enter).  The data and criteria ranges can be reversed in the AND test to check that each criteria exists in the data, rather than testing all the data against the criteria as above.


Testing a range of data ($A$2:$A$10)

for matching separately specified criteria

(e.g. $F22, $G22, $H22, $I22)

--------------------------------------------------------

[b]OR test:[/b] [code]=SUMPRODUCT(($A$2:$A$10=$F22)+($A$2:$A$10=$G22)+($A$2:$A$10=$H22)+($A$2:$A$10=$I22))>0

AND test:[/b] [code]=SUMPRODUCT(($A$2:$A$10=$F22)*($A$2:$A$10=$G22)*($A$2:$A$10=$H22)*($A$2:$A$10=$I22))>0 [i](testing for =1 would also work)

AND test, form 2:[/b] [code]=SUMPRODUCT(--($A$2:$A$10=$F22),--($A$2:$A$10=$G22),--($A$2:$A$10=$H22),--($A$2:$A$10=$I22))>0 [i](testing for =1 would also work)

single test (equivalent to countif>0):[/b] [code]=SUMPRODUCT(--($A$2:$A$10=$F22))>0


Commas in a SUMPRODUCT() formula mean multiply;

[code]--(test expression) coerces the true/false to a 1/0;

[code](test expression)+(test expression) coerces both to 1s/0s during arithmetic;

[code](test expression)*(test expression)

coerces both to 1s/0s during arithmetic;

You cannot SUMPRODUCT(test expression)[/code] or SUMPRODUCT((test expression),(test expression))[/code] because in the absence of the actual presence of the arithmetic operators, the trues/falses are not converted to 1s/0s and the formula won't work;

You can use NOT(test expression)[/code] to check that it is not a match.


You can combine multiple criteria in various combinations of ANDs/ORs (with optional NOTs).


Example--- (shorthand "dr1" = data range 1, "c1" = criteria 1):

dr1=c1 or dr2=c2, and dr3=c3:

=SUMPRODUCT((dr1=c1)+(dr2=c2),--(dr3=c3))>0[/code]

dr1=c1 or dr2=c2, and dr3=c3:

=SUMPRODUCT(((dr1=c1)+(dr2=c2))*(dr3=c3))>0[/code]

dr1=c1 or dr2=c2, but not both:

=SUMPRODUCT(((dr1=c1)+(dr2=c2))*NOT((dr1=c1)*(dr2=c2)))>0[/code]


Notice you can group sets of criteria with parenthesis.


dr1 and dr2 of course can be the same range in these examples.... or different ranges, and the same criteria... etc. imagination possible.


You can also combine the formulas for testing whole ranges of criteria at once with other single criteria or groups of criteria. First, an OR'ed range example, as this is simpler:

dr1=c1 or dr2=c2, and dr3=one or more criteria in cr3:[/i]

=SUMPRODUCT((dr1=c1)+(dr2=c2),--(dr3=cr3))>0[/code]


Since the formula I was able to come up with for testing a whole range of criteria with an "and" test doesn't work with SUMPRODUCT, but is instead an array formula (confirm entry with ctrl-shift-enter), if you want to combine it with other criteria, use SUM() instead of SUMPRODUCT, and only use * for "and"; don't use "," for and. Confirm entry with ctrl-shift-enter. Example:

dr1=c1 or dr2=c2, and each and every dr3=at least one cr3:[/i]

{ =SUM(((dr1=c1)+(dr2=c2))*AND(NOT(ISNA(MATCH(dr3,cr3,0)))))>0[/code] }[/b]


AND() and OR() often need to be avoided in array/sumproduct calculations, because they return a single result, not an array of results. However, this is desirable in this instance, and including the multi-criteria AND formula with other truth tests works as expected.


~ ~ ~ ~ ~ ~


I will be quiet until momi returns to the thread I've said plenty; I hope it's useful and helps you solve your problem.


Aas