Nested Ifs in Array formulas ?

  • Thread starter Thread starter twaccess
  • Start date Start date
T

twaccess

I've just learnt a little about Array formulas and am getting a bi
excited by the possibilities. Is it possible to write a nested i
formula that searches for a value in column a, then a different valu
in column b and then returns the sum of the value in column c ?

In effect I'm looking for 2 conditions to be met in columns a and
before it adds up the values in column c ?

Thanks

Terr
 
one way:

=SUMPRODUCT(--(A1:A100=cond1), --(B1:B100=cond2), C1:C100)


SUMPRODUCT is an array formula, but doesn't need to be array-entered. It
expects numeric arrays, which is why the unary minuses are used (to
coerce arrays of TRUE/FALSE to 1/0).
 
Hi
in this case you may use SUMPRODUCT. Enter this formula without
CTRL+SHIFT+ENTER (though SUMPRODUCT is an array function):
=SUMPRODUCT((A1:A1000=value1)*(B1:B1000=value2), C1:C1000)
 
Thank you both, I tried it out and it works a treat.

I work with large databases and keep thinking I ought to start usin
Access more than I do, only to find Excel has yet more tricks up it
sleeve and I just put the day off.

Until a couple of months ago I considered myself to be a power user o
Excel, but after spending some time looking through these boards I a
so wrong, but I have learnt so much as well.

Thanks

Terr
 
Back
Top