Sum If / Count If with Blank Cells

[bobbief123]

When I copy the trades into excel, every cell has a blank space before the data. I created column P and R to remove the space before
the profit and before the date. When I copy the formulas down for thousands of cells, it shows the #VALUE error. If I do the
IFERROR formula, my formulas count the "empty" cells as trades or wins. How do I get my formulas in cells U to work
without having errors in P and R? I put the correct answers in column W.

[AliGW]

Maybe this?

=IF(G34="","",SUBSTITUTE(G34,CHAR(160),"")+0)

[Pepe Le Mokko]

Your dates in column R still contain the time. Your date in R34 is 42788,38194 (in number format) where it should be 42788
You should extract the date only using the INT function
As my regional settings are not the same I can't post an example

[bobbief123]

It appears to work with the top formulas because the date is a bonus. The cells below, it still doesn't work. It still counts all of the cells as trades, even if they are blank and it counts them as wins.

[Pepe Le Mokko]

If you remove time as I suggested, it works perfectly

[bobbief123]

The top formulas work with the date. It is the bottom formulas that are not working and counting empty sales as trades, and wins.

[XLent]

fiddly to test in the UK - but, apply either AliGW suggestion or IFERROR to P/R and, modify your FREQUENCY arrays to leverage N (so as to discount the precedent Null strings), e.g.:

Please Login or Register  to view this content.

there are alternate approaches you could take, to limit how much you're looking at relevant to available values, but in terms of immediate fix - try above.