SUMIF(and) Formula Problem

[madhunt]

Hi All,

I am trying to write a SUMIF(and) formula, but it just doesn't seem to work... Am I missing something in the formula below? No matter how much I stare at it, I just can't understand why it isn't working...

{=SUM(($AG:$AG="*"&C3&"*")*($U:$U=D2)*$A:$A)}

I've set it up as an array formula...

Many thanks in advance

 

[Aladin Akyurek]

Hi All,

I am trying to write a SUMIF(and) formula, but it just doesn't seem to work... Am I missing something in the formula below? No matter how much I stare at it, I just can't understand why it isn't working...

{=SUM(($AG:$AG="*"&C3&"*")*($U:$U=D2)*$A:$A)}

I've set it up as an array formula...

Many thanks in advance

If you are on 2007 or later...

=SUMIFS($A:$A,$U:$U,D2,$AG:$AG,"*"&C3&"*")

On all systems...

=SUM(IF($U$2:$U$2000=D2,IF(ISNUMBER(SEARCH(C3,$AG$2:$AG$2000)),$A$2:$A$2000)))

which needs to be confirmed with control+shift+enter, not just enter.

 

[madhunt]

Thank you very much.... Your formula worked a treat...

It's worth me pointing out that I slightly altered it to:

=SUM(IF($U$2:$U$2000=D2,IF(ISNUMBER(SEARCH("*"&C3&"*",$AG$2:$AG$2000)),$AI$2:$AI$2000)))

The part i changed was:
("*"&C3&"*",$AG$2:$AG$2000) because I am searching within the cells for a partial word...

But, as said, your magic worked a treat!

 

[Aladin Akyurek]

Thank you very much.... Your formula worked a treat...

It's worth me pointing out that I slightly altered it to:

=SUM(IF($U$2:$U$2000=D2,IF(ISNUMBER(SEARCH("*"&C3&"*",$AG$2:$AG$2000)),$AI$2:$AI$2000)))

The part i changed was:
("*"&C3&"*",$AG$2:$AG$2000) because I am searching within the cells for a partial word...

But, as said, your magic worked a treat!

Great. Thanks for providing feedback. By the way, SEARCH(C3,X) and SEARCH("*"&C3&"*",X) should behave the same way.