Excel: two criteria in Sum.as 1

[Sashanan]

In Excel 2000, I am using SUM.AS for a calculation. (Or at least I assume that's what it's called, using a foreign version of Office.)

On my sheet, I have two columns which can contain various text values, J and K, followed by any number of numeric value columns. What I need is to find a formula that will add up the numeric values in a column for every row where J matches 'IV' AND K matches 'EG'.

I've figured out how to get it to check either condition, e.g.:

=sum.as(k1:k10;"EG";M1:m10)
+ sum.as(j1:j10;"IV";m1:m10)

This is adding up column M and assuming 10 values. Easy to adapt with more rows or for different columns. However, this one will add up if either condition (J="IV" or K="EG&quot is true, and I want it to add only if they are both met at the same time.

I've played around with it and read the help files, but can't seem to figure out how to check more than one condition. As far as I know it might not even be possible with sum.as.

Can anybody help me out here?
"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[xlbo]

I'm presuming that SUM.AS is equivalent to SUMIF in the UK / US versions

You will need to use an ARRAY formula. These are entered with CTRL+SHIFT+ENTER as opposed to just ENTER

formula would be
=SUM((J1:J10="IV&quot*(K1:K10="EG&quot*(M1:M10))

Hope that SUM is the same in both versions
If you have done it correctly, { } will appear automatically around the formula after entry to indicate that it is an array formula

One thing to be aware of is that if you have ANY error values iw DIV/0! #VALUE etc, in any of the referenced ranges,the formula will not work Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Sashanan]

Yeah, it probably is SUMIF, sounds more logical in hindsight. My Dutch version calls it SOM.ALS, and 'als' can mean either 'as' or 'if'. Thanks for clearing that one up.

The array formula's working fine too, thanks a lot!
"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)