Nested SUMIF? or SUM with multiple conditionals?

[marie]

Not sure if you can help me on this formula dilemma...

Here's what I want to see but this formula doesn't work:
{=SUM((SW!$I$4:$I$49,"=Client Financial Issues ")*(SW!$H$4:$H$49="lost"),SW!$D$4:$D$49)}

In english:
If range I4:I49 on the SW tab equals Client Financial Issues AND range H4:h49 equals Lost, then sum the amount in column D

In plain english, I want to know the reasons of lost accounts only and with their $ volumes.

If you could help, I would be grateful.
THANK YOU.

 

[Aladin Akyurek]

=SUMPRODUCT(--(SW!$I$4:$I$49="Client Financial Issues"),--(SW!$H$4:$H$49="lost"),SW!$D$4:$D$49)

which you need to confirm with just enter.

 

[marie]

Wow! I've been struggling with this for some time. I should've joined a long time ago. Thank you so very much! That did the trick!

 

[headtoadie]

Aladin, can you explain the syntax on this. Particulary what do the "--" do?

HT

 

[just_jon]

The -- bit acts as a coercer of TRUE/FALSE into 1/0 in the test condition arrays, so that you have numeric representations of the tests for use in further calculations.

 

[headtoadie]

Usually I use SUM(IF(condition1*condition2),sumrange) arrays. But this seems more straight forward.

thanks,

HT

 

[oodai]

HI,

I am looking for a formula or macro for nest sum if ihave built this formula but doesnt work..

=SUMPRODUCT(--(Sheet1!H:H="244","468"),--(Sheet1!G:G="63244","20468","00468"),--(Sheet1!N:N="Marges"),--(Sheet1!$S$2:$S$3000) basically what it should do is take search for 244 and 468 in 1st column and =63244,20468,00468 from column 2 and "marges" in column 3 and sum in column 4(SOLDe row header) and is there a way that i can give parameters and and total autmatically generated for example ="244","468" can change