sumif statement

[Karen Smith]

I need to write a formula that does the following:

If column C = AP and somewhere in column M it says fee, then put the sum of
column HA here.

Please note, that column M might says Agency Fee or DG Fee or System Fee or
just Fee......but somewhere in the cells in column M the word Fee appears.
Is there a way to do this?

 

[Dave Peterson]

Maybe...

=sumproduct(--(c1:c100="AP"),--(isnumber(search("fee",M1:m100))),(ha1:ha100))

 

[PCLIVE]

One possible way if I understand you correctly:

=IF(AND(C1=AP1,COUNTIF(M:M,"Fee")>0),SUM(HA:HA),"")

 

[Dave Peterson]

I was confused by the question. But if your answer is correct, then I think:

=IF(AND(C1=AP1,COUNTIF(M:M,"*Fee*")>0),SUM(HA:HA),"")

Would be closer.

 

[Dave Peterson]

Some more info:

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html