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?

Maybe...

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



Karen Smith wrote:

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

One possible way if I understand you correctly:

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


"Karen Smith" wrote in message
...
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?

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.

PCLIVE wrote:

One possible way if I understand you correctly:

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

"Karen Smith" wrote in message
...
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

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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



Dave Peterson wrote:

Maybe...

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

Karen Smith wrote:

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

--

Dave Peterson