A little direction...

[brandonrlz]

First...best find!! The site and video's are pure awesome and help provide me with a lot of refresher courses as well as new tactics so thanks to everyone!!

Here's my dilemma...I did some searching but couldn't really find anything that really targets what I'm trying to do, so any direction on how to approach this would be fantastic!!

I've got three columns in a spreadsheet that I want to slim down to one level and use some conditional formatting to build a dashboard out of it. That part won't be to difficult to do; however, I'm having a little bit of a brain fart in how to accomplish this.

Column 1 - Column 2 - Column 3
Bob - 1 - 1
Bob - 2 - 2
Bob - 3 - 3
Bill - 4 - 4
Bill - 5 - 5
Bill - 6 - 6
Steve - 7 - 7
Steve - 8 - 8
Steve - 9 - 9

What I want to do is take each line item and combine/calculate it into one line item.

Bob - 12
Bill - 30
Steve - 48

I've tried using "sumif" to do this; however, it's only capturing the first column of data.

=SUMIF(A1:A9,"Bob",B1:C9)

I'm wondering if doing an array would work better in this situation, or if I should be taking a different approach entirely.

Thanks for the help and insight on this.

 

[brandonrlz]

Here you go.

Excel Workbook
	A	B
26	Name	Range
27	custJan2011	='Sheet1'!$A$9:$A$1338
28	custCMBJan2011	='Sheet1'!$C$9:$D$1338
Sheet4

Here's a snippet of the data within the name range:

Sheet 1

Excel Workbook
	A	C	D
9	Customer A	4,548,778	3,539,052
10	Customer A	4,548,778	3,539,052
11	Customer A	4,548,820	3,539,052
12	Customer A	315,332	257,639
13	888	694,936	7,051
14	888	74,631	6,811
15	888	803,193	284,351
Sheet

 

[brandonrlz]

I think I understand what the problem is...because there are duplicates it's adding those additional values; however, here's more of the snippet of the RAW that get's spit out to me. I've been ignoring the "B" column as it's not really important to me in this particular formula.

Excel Workbook
	A	B	C	D
9	Customer A	p001	4,548,778	3,539,052
10	Customer A	p002	4,548,778	3,539,052
11	Customer A	p003	4,548,820	3,539,052
12	Customer A	test	315,332	257,639
13	888	p001	694,936	7,051
14	888	p002	74,631	6,811
15	888	p003	803,193	284,351
Sheet 1

 

[snoopyhr]

{=IF(ROWS($A6:A6)>$A$1,"",INDEX(custJan2011,SMALL(IF(FREQUENCY(IF(custJan2011<>"",MATCH(custJan2011&"",custJan2011&"",0)),ROW(custJan2011)-ROW('PEPM Extract 2011'!$A$9)+1),ROW(custJan2011)-ROW($A6)+1),ROWS($A6:A6))))}


ROW('PEPM Extract 2011'!$A$9)+1)

what is this in relation to the formula? why A9 ?
ROW(custJan2011) <----- range used is it in any relation to PEPM Extract 2011'! ?

it is hard to follow your logic as to what these ranges refer to - the ranges you show fail to this any relation to the above. I am stumped

 

[brandonrlz]

Sorry let me add some clarification and additional data that will hopefully help.

Excel Workbook
	A	B	C	D
7	Jan 2011
8	Customer Instance	Profile	Value 1	Value 2
9	Customer A	p001	4,548,778	3,539,052
10	Customer A	p002	4,548,778	3,539,052
11	Customer A	p003	4,548,820	3,539,052
12	Customer A	test	315,332	257,639
13	888	p001	694,936	7,051
14	888	p002	74,631	6,811
15	888	p003	803,193	284,351
Sheet 1

Excel Workbook
	A	B
26	Name	Range
27	custJan2011	='Sheet1'!$A$9:$A$15
28	custCMBJan2011	='Sheet1'!$C$9:$C$15
Sheet4

Excel Workbook
	A	B
1	72
2
3
4	Jan-11
5	Customer	Combined Values
6	Customer A	24,836,503
7	888	102,911,262
Combined 2011

As mentioned its taking some of the values and duplicating it like that in my previous post. Does that help or make sense now?

 

[snoopyhr]

I fail to understand the way your range names are related to row counts. Hence I take a bow and leave the stage....

Click on the Post Reply button, and just put the word BUMP in the post. Then, click on the Submit Reply button, and someone else will assist you.

 

[brandonrlz]

Snoop - I stepped back and stripped everything out to recreate it in a basic setting just to get the concept of it all down.

Once that was done I added the puzzle pieces (name ranges) to it all. Doing that, I realized what I jacked up!! I'm glad I did that as well as taking a break from it....cause everything you pointed out and helped me with worked perfectly!

Thanks a TON for the help.

 

[snoopyhr]

Glad it worked You are welcome I am Honored to be an assistance