Multiple criteria Unique Counting

[barkley]

I have a data sheet of four columns.
The first two columns are used in an array on my summary sheet. Column A contains the column headings, column B contains the row headings of this array.
I need to use some form of COUNTIF or SUMPRODUCT to count the unique entries in column C for each combination of Column A & B on the data sheet.
So far on my summary sheet I have
=SUMPRODUCT((Data!A3:A20000=B3)*(Data!B3:B20000=A4),(Data!D3:D20000))
Which seems to return 0 and wouldn't account for unique references only anyway.
Does this make sense to anyone & can you help?
Thanks.

 

[barkley]

I have tried your solution, many thanks for your time, the formula using BigStr returns an N/A error (BigStr is defined as instructed).
I have attached a (condensed) sheet so far as per your instructions, but only the first group of cells works, and even then C4 should equal a total of 5 and C5 equals 6.
I have tried to work out how to resolve this but cannot see why it isn't working.
Any further suggestions?

Contractor and WO Count by Branch.xls
	A	B	C	D	E	F	G
1
2			3	328	784	15004	#N/A
3			AB	BA	BN	WE
4	BEQUIP	Contractors	1	#VALUE!	#VALUE!	#N/A
5		WONo	0	#VALUE!	#VALUE!	#N/A
6	BLDMNT	Contractors	#VALUE!	#VALUE!	#VALUE!	#N/A
7		WONo	#VALUE!	#VALUE!	#VALUE!	#N/A
8	BLDREP	Contractors	#VALUE!	#VALUE!	#VALUE!	#N/A
9		WONo	#VALUE!	#VALUE!	#VALUE!	#N/A
10	CASH	Contractors	#VALUE!	#VALUE!	#VALUE!	#N/A
11		WONo	#VALUE!	#VALUE!	#VALUE!	#N/A
Summary Feb,Mar,Apr

 

[just_jon]

I think those formulas require entry by holding down the CONTROL and SHIFT keys when hitting ENTER (=SUM(IF(FREQUENCY...).

 

[barkley]

I'm blaming it on Monday! I should've re read what I was told earlier.
Thanks for that.

Unfortunately the BigStr problem still applies, any ideas why it returns N/A?

 

[just_jon]

Is column A sorted, ascending order?

 

[barkley]

Yup.
Exactly as required.

 

[Aladin Akyurek]

Yup.
Exactly as required.

BigStr needs to be defined via Insert|Name|Define as referring to:

=REPT("z",255)

Is that the case?

 

[barkley]

No, I used 9.99999999999999E+307

I've just changed it to your suggestion and it works!

Thank you very much. It now takes 10 minutes to refresh instead of 10 hours!!

Greatly appreciated.

 

[Aladin Akyurek]

No, I used 9.99999999999999E+307

We use that big number (BigNum) for numeric ranges.

I've just changed it to your suggestion and it works!

Thank you very much.

You're welcome.

It now takes 10 minutes to refresh instead of 10 hours!! ...

Wow. What a difference! This made my day.