Distinct / Unique count nest formula debacle

  • Thread starter Thread starter dp_fulcher
  • Start date Start date
D

dp_fulcher

Hi there,

I am not able to input the following formula as I receive a messag
saying "The formula you typed contains an error", yet I struggle to se
where the problem lies.

The formula is:

=SUMIF(‘Sheet 4’!B1:B100,A4,SUMPRODUCT((‘Shee
4’!C1:C100<>"")/COUNTIF(‘Sheet 4’!C1:C100,’Sheet 4’!C1:C100&"")))

How can I modify this formula so that it does not contain an error, o
alternatively, what other methods are there for obtaining the sam
output
 
It's a little hard to see exactly what you are after, so
an exact alternative doesn't come to mind.

But, the SUMIF formula requires a range where you are
trying to use the SUMPRODUCT formula.

I suspect you'll do well to break this down into pieces
in multiple columns.
 
dp_fulcher > said:
I am not able to input the following formula as I receive a message
saying "The formula you typed contains an error", yet I struggle to see
where the problem lies.

The formula is:

=SUMIF(‘Sheet 4’!B1:B100,A4,SUMPRODUCT((‘Sheet
4’!C1:C100<>"")/COUNTIF(‘Sheet 4’!C1:C100,’Sheet 4’!C1:C100&"")))

How can I modify this formula so that it does not contain an error, or
alternatively, what other methods are there for obtaining the same
output?
Click to expand...

If you're really using screwball quote characters like the ones shown above,
that's your problem. Use standard ASCII single quotes as produced by the
unshifted key immediately to the left of the leftmost [Enter] key (the one
above the right [Shift] key). Also, you can't use SUMPRODUCT function calls
as the third argument to SUMIF. What are you trying to do?
 
Hi
please explain what you're trying to calculate. The last part seems to
calculate the number of unique entries in column C. But I don't know
why you're using a SUMIF at the beginning. Could be you wan a
conditional count of unique entries in column C?
 
What I am trying to do is select the records which match with cell A
and then perform a unique count on the selected records.

Does this make sense?

The quotation marks are in ASCII format in the formula, so that is no
an issue.

Given that it appears as though I am unable to have the SUMPRODUC
formula as the sum_range in the SUMIF statement, how can I get aroun
this
 
Hi
one way: Try the following array formula (entered with
CTRL+SHIFT+ENTER):
=SUM(1/MMULT(--(IF(‘Sheet 4’!B1:B100=A4,‘Sheet 4’
!C1:C100)=TRANSPOSE(IF(‘Sheet 4’!B1:B100=A4,‘Sheet 4’
!C1:C100))),ROW(C1:C100)^0))-1
 
What I am trying to do is select the records which match with cell A4
and then perform a unique count on the selected records.
Click to expand...
...

You mean select records in which column A in the table matches cell A4, then
count the distinct entries in column B in the table? If so, try the array
formula

=SUM(ISNUMBER(MATCH('Sheet 4'!C1:C100,IF('Sheet 4'!B1:B100=A4,
'Sheet 4'!C1:C100),0))/COUNTIF('Sheet 4'!C1:C100,'Sheet 4'!C1:C100&""))

Also, post in plain text so that single quotes appear as 'foo' rather than as
‘foo’, ‘foo‘, ’foo‘ or ’foo’. Doing so eliminates ambiguity.
 
Back
Top