CODEWITHSUNDEEP
excelexcel-formula
Jcortes
Jcortes

Reputation: 51

nested excel functions with conditional logic

Just getting started in Excel and I was working with a database extract where I need to count values only if items in another column are unique.

So- below is my starting point:

=SUMPRODUCT(COUNTIF(C3:C94735,{"Sharable Content Object Reference Model 1.2","Authored SCORM/AICC content","Authored External Web Content"}))

what i'd like to figure out is the syntax to do something like this-

=sumproduct (Countif range1 criteria..., where range2 criteria="is unique value")

Am I getting this right? The syntax is a bit confusing, and I'm not sure I've chosen the right functions for the task.

enter image description here

Upvotes: 0

Views: 268

Answers (2)

maybeWeCouldStealAVan
maybeWeCouldStealAVan

Reputation: 15610

I just had to solve this same problem a week ago.

This method works even when you can't always sort on the grouping column (J in your case). If you can keep the data sorted, @MikeD 's solution will scale better.

Firstly, do you know the FREQUENCY trick for counting unique numbers? FREQUENCY is designed to create histograms. It takes two arrays, 'data' and 'bins'. It sorts 'bins', then creates an output array that's one longer than 'bins'. Then it takes each value in 'data' and determines which bin it belongs in, incrementing the output array accordingly. It returns the array. Here's the important part: If a value appears in 'bins' more than once, any 'data' value meant for that bin goes in the first occurrence. The trick is to use the same array for both 'data' and 'bins'. Think it through, and you'll see that there's one non-zero value in the output for each unique number in the input. Note that it only counts numbers.

In short, I use this:

=SUM(SIGN(FREQUENCY(<array>,<array>)))

to count unique numeric values in <array>

From this, we just need to construct arrays containing numbers where appropriate and text elsewhere.

In the example below, I'm counting unique days when the color is red and the fruit is citrus:

conditional unique

This is my conditional array, returning 1 or true for the rows I'm interested in:

($A$2:$A$10="red")*ISNUMBER(MATCH($B$2:$B$10,{"orange","grapefruit","lemon","lime"},0))

Note that this requires ctrl-shift-enter to be used as an array formula.

Since the value I'm grouping by for uniqueness is text (as is yours), I need to convert it to numeric. I use:

MATCH($C$2:$C$10,$C$2:$C$10,0)

Note that this also requires ctrl-shift-enter

So, this is the array of numeric values within which I'm looking for uniqueness:

IF(($A$2:$A$10="red")*ISNUMBER(MATCH($B$2:$B$10,{"orange","grapefruit","lemon","lime"},0)),MATCH($C$2:$C$10,$C$2:$C$10,0),"")

Now I plug that into my uniqueness counter:

=SUM(SIGN(FREQUENCY(<array>,<array>)))

to get:

=SUM(SIGN(FREQUENCY(
    IF(($A$2:$A$10="red")*ISNUMBER(MATCH($B$2:$B$10,{"orange","grapefruit","lemon","lime"},0)),MATCH($C$2:$C$10,$C$2:$C$10,0),""),
    IF(($A$2:$A$10="red")*ISNUMBER(MATCH($B$2:$B$10,{"orange","grapefruit","lemon","lime"},0)),MATCH($C$2:$C$10,$C$2:$C$10,0),"")
)))

Again, this must be entered as an array formula using ctrl-shift-enter. Replacing SUM with SUMPRODUCT will not cut it.

In your example, you'd use something like:

=SUM(SIGN(FREQUENCY(
    IF(ISNUMBER(MATCH($C$3:$C$94735,{"Sharable Content Object Reference Model 1.2","Authored SCORM/AICC content","Authored External Web Content"},0)),MATCH($J$3:$J$94735,$J$3:$J$94735,0),""),
    IF(ISNUMBER(MATCH($C$3:$C$94735,{"Sharable Content Object Reference Model 1.2","Authored SCORM/AICC content","Authored External Web Content"},0)),MATCH($J$3:$J$94735,$J$3:$J$94735,0),"")
)))

I'll note, though, that scaling might be a problem on data sets as large as yours. I tested it on larger data sets, and it was fairly fast on the order of 10k rows, but really slow on the order of 100k rows, such as yours. The internal arrays are plenty fast, but the FREQUENCY function slows down. I'm not sure, but I'd guess it's between O(n log n) and O(n^2) depending on how the sort is implemented.

Maybe this doesn't matter - none of this is volatile, so it'll just need to calculate once upon refreshing the data. If the column data is changing, though, this could be painful.

Upvotes: 2

MikeD
MikeD

Reputation: 8941

Asuming the source data is sorted by the key value [A], start with determining the occurence of the key column

B2: =IF(A2=A1;B1+1;1)

Next determine a group sum

C2: =SUMIF($A$2:$A$9;A2;$B$2:$B$9)

A key is unique if its group sum is exactly 1

D2: =(C2=1)

To count records which match a certain criterium AND are unique, include column D in a =IF(AND(D2, [yourcondition];1;0) and sum this column

Another option is to asume a key unique within a sorted list if it is unequal to both its predecessor and successor, so you could find the unique records like 

E2: =AND(A2<>A1;A2<>A3)
G2: =IF(AND(E2;F2="this");1;0)

E and G can of course be combined into one single formula (not sure though if that helps ...)

G2(2): =IF(AND(AND(A2<>A1;A2<>A3);F2="this");1;0)

resolving unnecessarily nested AND's:

G2(3): =IF(AND(A2<>A1;A2<>A3;F2="this");1;0)

example

all formulas in row 2 should be copied down to the end of the list

Upvotes: 1

Related Questions