Counting By Criteria Within Another Count

[miwoodar]

Ugh! I can't seem to crack this no matter how hard I try and how much I search. If anyone has a good idea or solution, could you please help?

I'm trying to find a way to summarize a work breakdown structure. I have a single column of data and as many formula columns as I need. Possible entries into the data column include word Top, the abbreviation DCM, or a blank. The page might lay out like this with a bunch of blank cells smattered throughout the column...

Data: WBS1: WBS2:
Top 1
DCM 1
DCM 2
Top 2
DCM 1
DCM 2
DCM 3
Top 3
DCM 1
DCM 2
etc.....

I can keep track of the Tops no problem (=COUNTIFS(AA8:$AA$574,"Top"). In the next column I'm trying to write a formula that will count the DCMs. I need to assign a unique sequential number to each DCM that falls under that particular Top. Once the next Top starts, the DCM count needs to reset to zero.

I've been trying to use something like this...
=COUNTIF(INDIRECT("Aa1:A"&MATCH("TOP",AA4:AA571,0)-1),"DCM")

Also tried this...
=COUNTIF(AA4:INDIRECT("A"&MATCH("TOP",AA4:AA571,0)-1),"DCM")

Any ideas?

____________________
Mike

 

[SkipVought]

Hi,

Trouble is, you're working off a REPORT rather than the Source Data that the report was generated from.

It is Way Far Harder to analyse a report!!!

I'd move heaven and earth to find the source.

Short of that, in the first empty column to the right, propogate the TOP number thru ALL rows fo data. The use SUMPRODUCT to count the occurrence of any value. Assuming that the new column is ZZ
[tt]
=sumproduct(--($AA$4:$AA$571="DCM")*--($ZZ$4:$ZZ$571=1))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[miwoodar]

Thanks Skip -

I dropped it into the sheet but the sumproduct idea didn't seem to work...I'll play around with it more in the morning when I'm fresh.

Your comment regarding the source data is well taken. Unfortunately, the spreadsheets I'm working with are large tangles of engineering calculations. I'm actually trying to extract a WBS and summary information so I can then orient it into what would become the source data for my reporting needs.

____________________
Mike

 

[lionelhill]

This should work (it did for me):

B2 should contain something like
=if(A2="Top", B1+1, B1)
Copy this to the bottom of the table, and set B1 to your starting number(1)

This should give you a column of numbers for WBS1, except that you'll have a number in every cell. To make it look just like WBS1, put another column C with
=if(A1="Top", B1, "") which will make blanks except where a new "Top" section starts.

Hide column B if you like!

Now, in the next column (D) you need something like this in cell D2:
=IF(A2="Top", "", IF(AND(A2="DCM", A1="Top"), 1, D1+1))

Copy this to the bottom, and it will number WBS2 appropriately, restarting after each Top

I haven't so far dealt with blank entries. WBS1 is correct already. For WBS2, change D2 to the following:
=IF(A2="Top","",IF(AND(A2="DCM",A1="Top"),1,IF(A2="",D1,D1+1)))
This carries over numbering on blank rows without increasing the count.
Then add column E with a test whether column A is blank
=if(A1="","",D1) which should cut out all unwanted numbers on blank rows in D. Hide D!

Good luck

 

[SkipVought]

BTW, "=if(A2="Top", B1+1, B1)" and "Copy this to the bottom of the table" is wat I originally meant by, "propogate the TOP number thru ALL rows fo data..."

This is pretty simple stuff; not rocket science.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[miwoodar]

These solutions are a lot easier than the path I was going down! Thanks guys - I got it to work.

____________________
Mike