Is there any way I can do this

[Bryan De-Lara]

I use this formula
=SUMPRODUCT(--(E6:E2036=1),--(E7:E2037<>1),--($A6:$A2036>=TODAY()-365)) to
count which works great, this counts 1's. I now need to add a P or a UP to
the same cell as the 1. Is there anyway I can leave the formula above as it
is and also do a count on the P or UP? Not all cells will have the same
contents. I've tried using an array
=--{SUM(LEN(A8:A15)-LEN(SUBSTITUTE(A8:A15,"pl","")))/LEN("pl")} along with
the sumproduct formula to count both, but with no joy. I've tried all manor
of ways, all with the same results. Can anyone guide me please.

Bryan.

 

[Pete_UK]

Do you mean that cells in column E could be something like 1UP or 1P ?

Please give more details of what your data looks like.

Pete

 

[bryan.delara]

Do you mean that cells in column E could be something like 1UP or 1P ?

Please give more details of what your data looks like.

Pete




- Show quoted text -

Yes Pete, the whole column could have either 1, 1p or 1up or obviously
an empty cell. I count the 1's no problem on their own. The formula I
use for that counts either singularly or blocks. That part is great.
But if I try to add to that formula nothing works. I need to try and
let that formula work as it is now and count then to p & or the up and
have the result in E5. I can use the LEN to count the p but then the
other formula doesn't work.