sumif array function

Y

yshridhar

Can any one suggest me the modification for the formula please?

=SUM(IF((exp!$C$3:$C$3000={4,5})*(exp!$D$3:$D$3000="School")*(exp!$G$3:$G$3000="cash"),exp!$L$3:$L$3000))

In place of {4,5} can i give cell address say A1,B1. Then how the formula
looks like?
Sincere thanks to all
Sridhar
 
P

Peo Sjoblom

You don't need to array enter if you use

=SUMPRODUCT((exp!$C$3:$C$3000=A1:B1)*(exp!$D$3:$D$3000="School")*(exp!$G$3:$G$3000="cash")*exp!$L$3:$L$3000)


otherwise

=SUM(IF((exp!$C$3:$C$3000=A1:B1)*(exp!$D$3:$D$3000="School")*(exp!$G$3:$G$3000="cash"),exp!$L$3:$L$3000))
 
Y

yshridhar

Many Thanks to you Mr. Peo Sjoblom. Not only uour suggestion solved my
problem but also it reduced some of my very long formulae to fit into the
formula box. However sumproduct is not working with my data. I don't know
it may be due to my data. Any suggestions
My Sincere Thanks
Sridhar
 
B

Bob Phillips

Explain what not working means, give some examples, and check the data for
any anomalies.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Y

yshridhar

I am sorry i entered the formula wrongly. My sincere thanks to you Mr. Bob.
My heart felt thanks to Peo Sjoblom
Sorry for my mistake and inconvinience caused.
Sridhar
 
Top