Need a little Excel help.

[dbdg]

I have to use excel for something I am doing at work at the moment, pain in the arse but I have no choice.

I count and sum up quite a lot with arrays at the moment.

For example

={SUM((A1:A1000="B")*(B1:B1000="C")*(C1:C1000="D") )}

The problem occurs though when I only want C1:C1000 to count unique values.

I can count unique in one column s using

={SUM(N(FREQUENCY(A1:A1000,A1:A1000)>0))}

but I can't see a way to link the two. Any of you guys have a suggestion, other then for me to do it another way?

[GNEP]

Does SUMPRODUCT() work?

[dbdg]

Not quite what I need, that sums the two arrays but I need to actual count the number of rows that fit criteria, first colum = B, second = C and then third = D, but I need to replace = D with = unique.

I have a work around at the moment but it requires me to use a couple of formula, want to try and do it in one if possible without any vba.

[GNEP]

Thought about it some more, and think I understand what you are trying to do. You need a two extra columns. One with the array formula (in, for example cells D11000):


=FREQUENCY(C1:C1000,C1:C1000)

and another (in column E) that is

=Dn*IF(Dn=1,1,0) (where n is the row number obviously).

Then you can use your first formula on columns A, B and E. The problem I think was in mixing array formulas and "normal" ones.

I can attach an example if the above is not clear.

Gnep.

[dbdg]

Yeah if you could Gnep, I can't get the frequency part to work, my head has gone to mush.

Got it now, frequency appears to only work on numbers.

The last part enters into E a 1 for all unique values, here is a twist for you, how can I also include the duplicate values once in this column?

[GNEP]

Here you go - save it and remove the .zip extension to use it. I've made the assumption that your column C contains numerical data... hope this is OK.

Also when entering the array formula, highlight D11000 (B2:B14 in my sheet), enter the formula in the formula box, then hit "CTRL-SHIFT-ENTER" to get it in there.

[GNEP]

To include the duplicate entries only on the first occurence in column C (working on my spreadsheet layout now), use this:

=IF(B2<>0,A2,"")

in C2 and copy down to C14.

[GNEP]

Oh, and this will work with non-numerical values now

EDIT: No it won't

[dbdg]

You are a diamond gnep.

[GNEP]

No probs. Unfortunately I spend far too much of my working life in Excel.