I have a spreadsheet with formulae that I'm now trying to modify to only apply to filtered data. I've done the simpler AVG and SUM formulae (via SUBTOTAL and SUMPRODUCT) but I have two I just cannot figure out.
{=SUM(IFERROR(--LEFT(J4:J999;FIND("m";J4:J999)-1);0)) &"m - "&SUM(IFERROR(-SUBSTITUTE(MID(J4:J999;FIND("-";J4:J999);99);"b";"");0))&"b"}
and
=IFERROR((SUMIF(M4:M999;">0";L4:L999)+SUMIF(M4:M999;"<0";L4:L999)+SUM(M4:M999))/(SUMIF(M4:M999;">0";L4:L999)+SUMIF(M4:M999;"<0";L4:L999));"N/A")
Any help on either or both will be much appreciated
Bookmarks