The following formula is returning a #N/A error and I have no idea why. The
data and formatting of the table array and lookup values is the same. I've
tried it in both SUM and SUM(IF... variants (both array) with no luck.

Columns A & B have repeating values (table array); Columns C & D contain the
unique values of the A & B range (lookup values). The formulas in Column E
are supposed to return the count of unique loc/code within the A:B range.

Here's the range and formulas (ranges in formulas are longer than what's
depicted below).

A1 LOCATION B1 CODE C1 LOC D1
CODE E1
A2 123 MAIN STREET B2 12345 C2 123 MAIN STREET D2 12345 (formula)
A3 123 MAIN STREET B3 12345 C3 234 APPLE AVE D3 12345 (formula)
A4 234 APPLE AVE B4 12345

Both formulas:
{=SUM(($A$2:$A$18691=$C2)*($B$2:$B$18691=$D2))}
{=SUM(IF($A$2:$A$18691=$C2,IF($B$2:$B$18691=$D2,1, 0),0))}

I appreciate your help.
EU

They both work fine for me.

Is the value in column B formatted as General, not as text?

--
HTH

Bob Phillips

"Excel User" wrote in message
...
The following formula is returning a #N/A error and I have no idea why.
The
data and formatting of the table array and lookup values is the same. I've
tried it in both SUM and SUM(IF... variants (both array) with no luck.

Columns A & B have repeating values (table array); Columns C & D contain
the
unique values of the A & B range (lookup values). The formulas in Column E
are supposed to return the count of unique loc/code within the A:B range.

Here's the range and formulas (ranges in formulas are longer than what's
depicted below).

A1 LOCATION B1 CODE C1 LOC D1
CODE E1
A2 123 MAIN STREET B2 12345 C2 123 MAIN STREET D2 12345
(formula)
A3 123 MAIN STREET B3 12345 C3 234 APPLE AVE D3 12345
(formula)
A4 234 APPLE AVE B4 12345

Both formulas:
{=SUM(($A$2:$A$18691=$C2)*($B$2:$B$18691=$D2))}
{=SUM(IF($A$2:$A$18691=$C2,IF($B$2:$B$18691=$D2,1, 0),0))}

I appreciate your help.
EU

Hi,

Please try this formula

{=SUM(IF(($A$2:$A$18691=$C2)*($B$2:$B$18691=$D2),1 ,0))}

Regards,

Ashish

"Excel User" wrote:

The following formula is returning a #N/A error and I have no idea why. The
data and formatting of the table array and lookup values is the same. I've
tried it in both SUM and SUM(IF... variants (both array) with no luck.

Columns A & B have repeating values (table array); Columns C & D contain the
unique values of the A & B range (lookup values). The formulas in Column E
are supposed to return the count of unique loc/code within the A:B range.

Here's the range and formulas (ranges in formulas are longer than what's
depicted below).

A1 LOCATION B1 CODE C1 LOC D1
CODE E1
A2 123 MAIN STREET B2 12345 C2 123 MAIN STREET D2 12345 (formula)
A3 123 MAIN STREET B3 12345 C3 234 APPLE AVE D3 12345 (formula)
A4 234 APPLE AVE B4 12345

Both formulas:
{=SUM(($A$2:$A$18691=$C2)*($B$2:$B$18691=$D2))}
{=SUM(IF($A$2:$A$18691=$C2,IF($B$2:$B$18691=$D2,1, 0),0))}

I appreciate your help.
EU

Bob, Thanks for your help. I reformatted it, but still couldn't get it to
work. I ended up concatenating the string and then doing a simple COUNTIF
formula. Thanks again.

"Bob Phillips" wrote:

They both work fine for me.

Is the value in column B formatted as General, not as text?

--
HTH

Bob Phillips

"Excel User" wrote in message
...
The following formula is returning a #N/A error and I have no idea why.
The
data and formatting of the table array and lookup values is the same. I've
tried it in both SUM and SUM(IF... variants (both array) with no luck.

Columns A & B have repeating values (table array); Columns C & D contain
the
unique values of the A & B range (lookup values). The formulas in Column E
are supposed to return the count of unique loc/code within the A:B range.

Here's the range and formulas (ranges in formulas are longer than what's
depicted below).

A1 LOCATION B1 CODE C1 LOC D1
CODE E1
A2 123 MAIN STREET B2 12345 C2 123 MAIN STREET D2 12345
(formula)
A3 123 MAIN STREET B3 12345 C3 234 APPLE AVE D3 12345
(formula)
A4 234 APPLE AVE B4 12345

Both formulas:
{=SUM(($A$2:$A$18691=$C2)*($B$2:$B$18691=$D2))}
{=SUM(IF($A$2:$A$18691=$C2,IF($B$2:$B$18691=$D2,1, 0),0))}

I appreciate your help.
EU

Thanks for your help Ashish. I've put your formula in my Toolbox to use going
forward as the standard for this type of task. Unfortunately, it still
wouldn't work. The formula worked on a test list I created in another
worksheet, but it wouldn't work on my dataset, which leads me to believe
there was an issue with the underlying data itself. I ended up concatenating
the string and then using a COUNTIF formula. Thanks again.

"Ashish Mathur" wrote:

Hi,

Please try this formula

{=SUM(IF(($A$2:$A$18691=$C2)*($B$2:$B$18691=$D2),1 ,0))}

Regards,

Ashish

"Excel User" wrote:

The following formula is returning a #N/A error and I have no idea why. The
data and formatting of the table array and lookup values is the same. I've
tried it in both SUM and SUM(IF... variants (both array) with no luck.

Columns A & B have repeating values (table array); Columns C & D contain the
unique values of the A & B range (lookup values). The formulas in Column E
are supposed to return the count of unique loc/code within the A:B range.

Here's the range and formulas (ranges in formulas are longer than what's
depicted below).

A1 LOCATION B1 CODE C1 LOC D1
CODE E1
A2 123 MAIN STREET B2 12345 C2 123 MAIN STREET D2 12345 (formula)
A3 123 MAIN STREET B3 12345 C3 234 APPLE AVE D3 12345 (formula)
A4 234 APPLE AVE B4 12345

Both formulas:
{=SUM(($A$2:$A$18691=$C2)*($B$2:$B$18691=$D2))}
{=SUM(IF($A$2:$A$18691=$C2,IF($B$2:$B$18691=$D2,1, 0),0))}

I appreciate your help.
EU