sumif array function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

yshridhar - 13 Sep 2007 06:04 GMT

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

Reply to this Message

Peo Sjoblom - 13 Sep 2007 06:13 GMT

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))

Signature

Regards,

Peo Sjoblom

> Can any one suggest me the modification for the formula please?
>
[quoted text clipped - 4 lines]
> Sincere thanks to all
> Sridhar

Reply to this Message

yshridhar - 13 Sep 2007 09:20 GMT

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

> You don't need to array enter if you use
>
[quoted text clipped - 12 lines]
> > Sincere thanks to all
> > Sridhar

Reply to this Message

Bob Phillips - 13 Sep 2007 09:33 GMT

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

Signature

HTH

Bob

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

> 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
[quoted text clipped - 21 lines]
>> > Sincere thanks to all
>> > Sridhar

Reply to this Message

yshridhar - 17 Sep 2007 04:46 GMT

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

> Explain what not working means, give some examples, and check the data for
> any anomalies.
[quoted text clipped - 24 lines]
> >> > Sincere thanks to all
> >> > Sridhar

Reply to this Message