sumif based on if FORMULA contains

[shabangle]

Hi Everyone

How can I do a sumif based on whether the formulas of the cells in a range contain a certain element?

I have been given a spreadsheet with mixed formulas (created by a novice), such that I'd like to sum all the values that have $P in their formula. I approached it with:

Sumif(T4:T432,"*$P*",T4:T432) but this doesn't work. Any ideas?

Thanks,
Shabangle

 

[Mackers]

Hi

If you have Excel 2013 onwards, try this

=SUMPRODUCT(ISNUMBER(FIND("$P",FORMULATEXT(T4:T432)))*T4:T432)

 

[shabangle]

Hmmm. I have Excel 2016, but that's not working.

Find("$P",FORMULATEXT(T432)) works, so I think there's perhaps a tiny error in the sum product part? Adding array brackets doesn't help either.

I have some gaps too, so wonder whether they're tripping up the formula over a range?

 

[shabangle]

Apologies, that is now in fact working. Thank you very much!!

 

[Mackers]

No problem, glad to help