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.

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

On Nov 24, 6:14*pm, "Bryan De-Lara" wrote:
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.

On 25 Nov, 01:33, Pete_UK wrote:
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

On Nov 24, 6:14*pm, "BryanDe-Lara" wrote:



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.- Hide quoted text -

- 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.