Challenging complex formula- unique count for multiple criteria on several levels

[joannelittell]

Hello!

I need some help with a complex formula. I have thousands of rows of data that is continuously increasing.

There are two ways the data can be looked at (across Type only and across Type and Size combo).

I need to find the number of times for each a month/year and CP type in which there was a problem. Specifically, if a Type fails (once or more) on any of the series (1-6), then it needs to be counted ONLY ONCE.

In addition, for both these ways, the data needs to be parsed by month/year and CP type.

For Type Only
If everything is ok for that Type, then it is a "0" for that Type
If one or more problems are identified within a Type, then it needs to be counted only ONCE (no matter how many problems were identified)

For Type and Size combination
If everything is ok for that type and size combo, then it is counted as a "0"
If one or more problems are identified within a Type and Size combo, then it needs to be counted only ONCE (no matter how many problems were identified)

I am fine with adding columns to help with the formulas.

I included mock data along with what that data should look like. Its pretty complicated, so I colored the data to help identify the Type (color) and Type and Size (different color shading).

Note:
I updated the spreadsheet based on feedback for clarity.
- Type, Size, OK? (only descriptions), and Date can be anything. These values cannot be unsed within the formula.
- CP, OK? (only 'Yes'), and Series are "fixed". These values can be used within the formula.


This is really kicking my behind and I really need some experts to help figure it out.


Thanks in advance for helping me with this tough formula.
Joanne

[azumi]

Pls check the file, hope it works


Azumi

[hemesh]

Hello joannelittell , As per of your requirement if there is any problem it should be counted as once but here in your example file the counting is more than once in the month of March 2013.

[joannelittell]

@ Azumi
This is amazing, unfortunately i just used simple fake data for demonstration purposes only. Type, Size, Color, etc. can be anything (for instance, Type is a descriptive document name that can be anything). I have updated my data to be more clear as to what is variable and what is not.

Like i said, this is very complex.

Sorry for not being crystal clear I really appreciate your effort!



@ hemesh
I did have the wrong value in there. sorry for the confusion and good catch!! I updated the spreadsheet for clarity.

Thanks
Joanne

[hemesh]

Hello Find the attached

First You need to have unique values for Type Column and Unique values for Type and Size in another column then You can easily compile them. In unique values for type and size column you can combine two value by ampersand (symbol) like in A1 you have john & in B1 you have three in C1 type = A1&B1 and you will get johnthree

[joannelittell]

Hello Find the attached

First You need to have unique values for Type Column and Unique values for Type and Size in another column then You can easily compile them. In unique values for type and size column you can combine two value by ampersand (symbol) like in A1 you have john & in B1 you have three in C1 type = A1&B1 and you will get johnthree

The comparisions that you have created, you use the new column values specifically within the formula. what happens when more data is added? It looks like I would have to update the formula when data is added.

[hemesh]

Yes you need to add specific criteria otherwise you will not get the results. But with this formula you need to update in the top cell then you can drag down and across you don't need to create separate formula formula for each cell.

Other way to do is to create a separate sheet with all unique values which will update dynamically and then sum the values. which will update when data is added to main sheet.

[joannelittell]

OK, I am currently working on something similiar to your second paragraph.

I will keep at it. thanks!!

[Max, Singapore]

In your sheet, try this formulas play which seems to give the desired results indicated
Note: Along the way I did assume that Type or Size will not duplicate across CP ...

Put in U3: =IF(C3="","",IF(COUNTIF(C$3:C3,C3)>1,"",ROWS($1:1)))
Copy to V3, fill down to V60, ie cover the extent of data for Type and Size. The above 2 cols will be referred to/used later to extract the uniques listing for Type and Size

Types
---------
Put in W2:
=IF(COLUMNS($A:A)>COUNT($U$3:$U$60),"",INDEX($C$3:$C$60,SMALL($U$3:$U$60,COLUMNS($A:A))))
Copy across to say, AC2. This lists all the unique Types, neatly packed to the left

Put in W1: =IF(W2="","",INDEX($B:$B,MATCH(W2,$C:$C,0)))
Copy across to AC1 (The note on the assumption made applies here)

Now to populate the checks by the mth/yr, reference is made to the time periods header data that is in I4:I15

Put in W3:
=IF(SUMPRODUCT(($C$3:$C$60=W$2)*(TEXT($G$3:$G$60,"mmm yyyy")=TEXT($I4,"mmm yyyy"))*(TRIM($F$3:$F$60)<>"Yes"))>0,W$1,"")
Copy across / fill down to AC14

Size
----------
Put in AE2:
=IF(COLUMNS($A:A)>COUNT($V$3:$V$60),"",INDEX($D$3:$D$60,SMALL($V$3:$V$60,COLUMNS($A:A))))
Copy across to say, AO2. This lists all the unique Sizes, neatly packed to the left

Put in AE1: =IF(AE2="","",INDEX($B:$B,MATCH(AE2,$D:$D,0)))
Copy across to AO1 (The note on the assumption made applies here)

Similarly, as done for Types, to populate the checks by the mth/yr, reference is made to the time periods header data that is in I4:I15

Put in AE3:
=IF(SUMPRODUCT(($D$3:$D$60=AE$2)*(TEXT($G$3:$G$60,"mmm yyyy")=TEXT($I4,"mmm yyyy"))*(TRIM($F$3:$F$60)<>"Yes"))>0,AE$1,"")
Copy across / fill down to AO14

With the above constructs done,
Put in J4: =COUNTIF($W3:$AC3,J$3)
Copy across / fill down to L15 to get the results for "Across Type only"

Similarly for Size ....
Put in O4: =COUNTIF($AE3:$AO3,O$3)
Copy across / fill down to Q15 to get the other set of results
-------------------------------------------------------
Success? Wave it, hit the little star at the bottom left of my responses

[joannelittell]

@ Max

Oh ... my... this works!! I will need to examine it a lot closer to fully understand everything that you did... but that is amazing!
I will need to peck at it for a bit to get it to work for all the data that will be added when it is collected. I will probably end up with thousands of data points.

But wow- incredible.

thanks for putting your brain to work on this!

Joanne

[Max, Singapore]

Welcome, glad to hear

[Max, Singapore]

@Joanne,
- Please note corrections for some earlier mis-alignments just detected (apologies): Steps and formulae have been revised in my post.