Ceiling formula

Hi,

I'm currently using the following formula:-
=CEILING(SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&"")),1)

It seems to be working, but if counting a few less items than there actually
are.

Does anyone know if there are flaws with this formula?

I've checked manually and the count if a few numbers out. The cells it is
reading are correct and I do the control, alt shift to make it work...

any thoughts would be much appreciated

thanks
fiona

Normal entered formula to count the number of distinct items in J10:H240 with
the criteria that B10:B240 = A4

=SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&""))

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi,

I'm currently using the following formula:-
=CEILING(SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&"")),1)

It seems to be working, but if counting a few less items than there actually
are.

Does anyone know if there are flaws with this formula?

I've checked manually and the count if a few numbers out. The cells it is
reading are correct and I do the control, alt shift to make it work...

any thoughts would be much appreciated

thanks
fiona

Thanks Jacob,

I still seem to be a few out. Would I have to change the format of the
cells or something like that? Your formula did work, but the problem I had
is still the same.



"Jacob Skaria" wrote:

Normal entered formula to count the number of distinct items in J10:H240 with
the criteria that B10:B240 = A4

=SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&""))

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi,

I'm currently using the following formula:-
=CEILING(SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&"")),1)

It seems to be working, but if counting a few less items than there actually
are.

Does anyone know if there are flaws with this formula?

I've checked manually and the count if a few numbers out. The cells it is
reading are correct and I do the control, alt shift to make it work...

any thoughts would be much appreciated

thanks
fiona

Hi Jacob,

I just had a thought, if I was counting information that had part of one
cell in another also, would the count miss it?

ie. one cell has 'roberts' the next cell has 'robertson' would it omit the
first 'roberts' thinking it was the same as 'robertson'?
Thanks

"Jacob Skaria" wrote:

Normal entered formula to count the number of distinct items in J10:H240 with
the criteria that B10:B240 = A4

=SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&""))

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi,

I'm currently using the following formula:-
=CEILING(SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&"")),1)

It seems to be working, but if counting a few less items than there actually
are.

Does anyone know if there are flaws with this formula?

I've checked manually and the count if a few numbers out. The cells it is
reading are correct and I do the control, alt shift to make it work...

any thoughts would be much appreciated

thanks
fiona

--If A4 is a text string check out whether the text strings in B10:B240 is
exactly same. (no leading, trailing spaces )

--Any formulas returning a space " " instead of "" blank

--Try out the same formula in a small set of manually entered values..to see
that it works fine.

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Thanks Jacob,

I still seem to be a few out. Would I have to change the format of the
cells or something like that? Your formula did work, but the problem I had
is still the same.



"Jacob Skaria" wrote:

Normal entered formula to count the number of distinct items in J10:H240 with
the criteria that B10:B240 = A4

=SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&""))

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi,

I'm currently using the following formula:-
=CEILING(SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&"")),1)

It seems to be working, but if counting a few less items than there actually
are.

Does anyone know if there are flaws with this formula?

I've checked manually and the count if a few numbers out. The cells it is
reading are correct and I do the control, alt shift to make it work...

any thoughts would be much appreciated

thanks
fiona

No. This will be considered as two entries..Like mentioned in the previous
post even spaces count..Try out the same formula with a small set of data to
see how it works..

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi Jacob,

I just had a thought, if I was counting information that had part of one
cell in another also, would the count miss it?

ie. one cell has 'roberts' the next cell has 'robertson' would it omit the
first 'roberts' thinking it was the same as 'robertson'?
Thanks

"Jacob Skaria" wrote:

Normal entered formula to count the number of distinct items in J10:H240 with
the criteria that B10:B240 = A4

=SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&""))

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi,

I'm currently using the following formula:-
=CEILING(SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&"")),1)

It seems to be working, but if counting a few less items than there actually
are.

Does anyone know if there are flaws with this formula?

I've checked manually and the count if a few numbers out. The cells it is
reading are correct and I do the control, alt shift to make it work...

any thoughts would be much appreciated

thanks
fiona

Thanks Jacob,

I've checked the boxes and can't see any that have any extra spaces or
different features. I've also checked the formula on a small area and it
works fine.

I must be missing something but cannot figure out what it is.

Thanks for your help.


"Jacob Skaria" wrote:

--If A4 is a text string check out whether the text strings in B10:B240 is
exactly same. (no leading, trailing spaces )

--Any formulas returning a space " " instead of "" blank

--Try out the same formula in a small set of manually entered values..to see
that it works fine.

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Thanks Jacob,

I still seem to be a few out. Would I have to change the format of the
cells or something like that? Your formula did work, but the problem I had
is still the same.



"Jacob Skaria" wrote:

Normal entered formula to count the number of distinct items in J10:H240 with
the criteria that B10:B240 = A4

=SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&""))

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi,

I'm currently using the following formula:-
=CEILING(SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&"")),1)

It seems to be working, but if counting a few less items than there actually
are.

Does anyone know if there are flaws with this formula?

I've checked manually and the count if a few numbers out. The cells it is
reading are correct and I do the control, alt shift to make it work...

any thoughts would be much appreciated

thanks
fiona

The below formula with the below data retrievs 2 if C1 = 1 and 1 if C1=2.
Isnt that what we should expect??

=CEILING(SUMPRODUCT(--($A$1:$A$10=C1),1/COUNTIF($B$1:$B$10,$B$1:$B$10&"")),1)

Col A Col B
1 AA
2 BA
2 BA
1 BA



If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi Jacob,

I think I've figured out what the problem is....I'm not sure of the solution
though?

The formula all works fine until I have an entry in coloumn J that is
entered twice where coloumn B is different. for example,
coloumn A coloum J
1 AA
2 BA
2 BA
All works fine but if....
1 AA
2 BA
2 BA
1 BA

It starts to miss a few out?

Any ideas how to get around this?

Many thanks

Fiona

"Jacob Skaria" wrote:

No. This will be considered as two entries..Like mentioned in the previous
post even spaces count..Try out the same formula with a small set of data to
see how it works..

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi Jacob,

I just had a thought, if I was counting information that had part of one
cell in another also, would the count miss it?

ie. one cell has 'roberts' the next cell has 'robertson' would it omit the
first 'roberts' thinking it was the same as 'robertson'?
Thanks

"Jacob Skaria" wrote:

Normal entered formula to count the number of distinct items in J10:H240 with
the criteria that B10:B240 = A4

=SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&""))

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi,

I'm currently using the following formula:-
=CEILING(SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&"")),1)

It seems to be working, but if counting a few less items than there actually
are.

Does anyone know if there are flaws with this formula?

I've checked manually and the count if a few numbers out. The cells it is
reading are correct and I do the control, alt shift to make it work...

any thoughts would be much appreciated

thanks
fiona

Hi, yes, that's right, I went back to my spreadsheet and entered the data
line by line so I could see at what point the formula got confused...it only
seemed to be when I had 2 items in coloum B (if using the below example) that
were the same, but the item in coloumn A was different, that the problem
started, before that all was working fine.

After that there were a few results less than there should be in the total?

I did it a bit at time, keeping all the items in coloumn A the same but
leaving coloum B asi it was, then another formula for the next lot of
comloumn A's that were the same and it all worked. When I combine coloumn A
with different items, that's when it seems to get muddled. It's strange
though as it's only a few numbers less that what it should be.

Maybe there is something else that I'm missing, but that is the only thing
I've found so far.

"Jacob Skaria" wrote:

The below formula with the below data retrievs 2 if C1 = 1 and 1 if C1=2.
Isnt that what we should expect??

=CEILING(SUMPRODUCT(--($A$1:$A$10=C1),1/COUNTIF($B$1:$B$10,$B$1:$B$10&"")),1)

Col A Col B
1 AA
2 BA
2 BA
1 BA



If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi Jacob,

I think I've figured out what the problem is....I'm not sure of the solution
though?

The formula all works fine until I have an entry in coloumn J that is
entered twice where coloumn B is different. for example,
coloumn A coloum J
1 AA
2 BA
2 BA
All works fine but if....
1 AA
2 BA
2 BA
1 BA

It starts to miss a few out?

Any ideas how to get around this?

Many thanks

Fiona

"Jacob Skaria" wrote:

No. This will be considered as two entries..Like mentioned in the previous
post even spaces count..Try out the same formula with a small set of data to
see how it works..

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi Jacob,

I just had a thought, if I was counting information that had part of one
cell in another also, would the count miss it?

ie. one cell has 'roberts' the next cell has 'robertson' would it omit the
first 'roberts' thinking it was the same as 'robertson'?
Thanks

"Jacob Skaria" wrote:

Normal entered formula to count the number of distinct items in J10:H240 with
the criteria that B10:B240 = A4

=SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&""))

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi,

I'm currently using the following formula:-
=CEILING(SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&"")),1)

It seems to be working, but if counting a few less items than there actually
are.

Does anyone know if there are flaws with this formula?

I've checked manually and the count if a few numbers out. The cells it is
reading are correct and I do the control, alt shift to make it work...

any thoughts would be much appreciated

thanks
fiona

Try the below array formula which uses FREQUENCY()..instead and feedback

=SUM(IF(FREQUENCY(IF(A$1:A$10=C1,MATCH(B$1:B$10,B$ 1:B$10,0)),ROW(A$1:A$10)-ROW(A$1)+1),1))

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi Jacob,

I think I've figured out what the problem is....I'm not sure of the solution
though?

The formula all works fine until I have an entry in coloumn J that is
entered twice where coloumn B is different. for example,
coloumn A coloum J
1 AA
2 BA
2 BA
All works fine but if....
1 AA
2 BA
2 BA
1 BA

It starts to miss a few out?

Any ideas how to get around this?

Many thanks

Fiona

"Jacob Skaria" wrote:

No. This will be considered as two entries..Like mentioned in the previous
post even spaces count..Try out the same formula with a small set of data to
see how it works..

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi Jacob,

I just had a thought, if I was counting information that had part of one
cell in another also, would the count miss it?

ie. one cell has 'roberts' the next cell has 'robertson' would it omit the
first 'roberts' thinking it was the same as 'robertson'?
Thanks

"Jacob Skaria" wrote:

Normal entered formula to count the number of distinct items in J10:H240 with
the criteria that B10:B240 = A4

=SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&""))

If this post helps click Yes
---------------
Jacob Skaria


"Fiona Yorke-Saville" wrote:

Hi,

I'm currently using the following formula:-
=CEILING(SUMPRODUCT(--(A4=$B$10:$B$240),1/COUNTIF($J$10:$J$240,$J$10:$J$240&"")),1)

It seems to be working, but if counting a few less items than there actually
are.

Does anyone know if there are flaws with this formula?

I've checked manually and the count if a few numbers out. The cells it is
reading are correct and I do the control, alt shift to make it work...

any thoughts would be much appreciated

thanks
fiona