Summarize Data by Person, Month and Percentages

**snapfade** · 06-23-2018, 07:24 PM

The Static table has a value in some or all months. On the right side of the 12 months are contractor names and the credit they get for each issue. On some issues two contractors share in the credit, in others just one contractor gets 100%.

The contractor gets a monthly credit that is multiplied by the Odds percent and the contractor’s percent.

For example, the first row of the table, issue 338-Chippawa Falls has a Nov value of 22,162. Amy’s share is 95% times 50% or 10,527.

For the entire month of Nov, Amy has credits for eight other issues with varying Odds, Shares and Amounts. Highlighted in yellow. Amy’s share for Nov is initially 140,588 as highlighted on the temporary Amy Helper Table off to the right.

Finally, on the Summary tab, for Amy and Nov the amount of 140,588 is further multiplied against the Pct value, 95%, giving Amy a final Nov credit of 133,588.

Easy Peasy.

Now, dear readers, the challenge is to fill out the Summary table for all contractors for every month…without using a Helper Table for each contractor.

Can this be done?

Thanks for taking the considerable time to get acquainted with my problem. Your suggestions would be most appreciated.

tom

**bebo021999** · 06-23-2018, 10:20 PM

Try in C2:

Please Login or Register  to view this content.

P/S:Contractor Irvin in C10 sheet Summary should be Irwin.

**snapfade** · 06-25-2018, 05:58 PM

Hi bebo,

Thanks for the suggestion. Looks complicated. Let me play with it and report back.

Thanks again!

tom

**snapfade** · 06-25-2018, 06:36 PM

Hey Bebo,

It WORKS Preferctly for Jan, but not when copied to other months the results are wrong. I tried to see where your formula could be modified to copy over to the months but i confess the formula is a bit beyond me.

2018-06-25_15-31-01.jpg

Would you kindly explain it to me?

Thanks!
tom

**FlameRetired** · 06-25-2018, 07:15 PM

snapfade the formula bebo gave you has tblData[Odds] in the first argument. The pic above shows tblData[Jan]. Try changing that an let us know what you get.

At my end I get:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Contractor	Pct	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
2	Amy	95%	27,899	21,702	18,444	19,299	50,200	45,551	47,628	36,331	54,495	88,191	133,558	111,798	655,095
3	Bill	110%	15,520	14,158	32,204	53,728	74,065	57,840	97,362	129,150	178,418	234,587	312,676	276,330	1,476,038
4	Chelsea	95%	70,664	82,545	88,880	135,552	97,227	118,879	146,025	103,830	122,111	333,749	311,769	352,702	1,963,934
5	David	100%	11,046	24,694	45,100	62,840	100,954	99,351	150,778	175,877	187,250	211,976	248,145	342,587	1,660,597
6	Eddie	100%	36,264	63,407	84,875	65,394	59,872	41,142	145,429	208,413	182,187	217,042	311,396	305,047	1,720,469
7	Fran	95%	11,105	18,840	26,023	49,966	76,306	45,359	161,152	175,454	188,380	243,908	217,123	186,828	1,400,445
8	Glenn	100%	3,682	25,302	40,199	59,949	58,454	55,160	145,494	196,868	217,364	247,151	326,777	330,624	1,707,025
9	Helen	85%	0	0	0	14,582	29,911	14,708	62,068	136,461	134,097	267,324	278,558	227,284	1,164,995
10	Irwin	95%	29,714	55,708	48,875	98,093	84,747	116,007	155,003	216,548	226,587	253,227	387,351	346,623	2,018,484
11	Julie	100%	19,776	41,048	35,535	65,716	72,336	45,781	111,183	80,141	67,480	141,027	197,589	168,677	1,046,290
12	Ken	110%	0	0	0	46,133	47,811	28,459	84,393	89,523	100,222	183,420	172,030	278,282	1,030,272
13	Linda	100%	20,344	18,867	23,774	81,926	49,163	50,668	71,249	85,131	134,599	122,411	172,323	192,398	1,022,854

**FDibbins** · 06-25-2018, 07:16 PM

I dont get anything like what your image shows...

	A	B	C	D	E	F	G
1	Contractor	Pct	Jan	Feb	Mar	Apr	May
2	Amy	95%	27,899	21,702	18,444	19,299	50,200
3	Bill	110%	15,520	14,158	32,204	53,728	74,065
4	Chelsea	95%	70,664	82,545	88,880	135,552	97,227
5	David	100%	11,046	24,694	45,100	62,840	100,954
6	Eddie	100%	36,264	63,407	84,875	65,394	59,872
7	Fran	95%	11,105	18,840	26,023	49,966	76,306
8	Glenn	100%	3,682	25,302	40,199	59,949	58,454
9	Helen	85%	0	0	0	14,582	29,911
10	Irwin	95%	29,714	55,708	48,875	98,093	84,747
11	Julie	100%	19,776	41,048	35,535	65,716	72,336
12	Ken	110%	0	0	0	46,133	47,811
13	Linda	100%	20,344	18,867	23,774	81,926	49,163

**FlameRetired** · 06-25-2018, 07:22 PM

@ snapfade

.... i confess the formula is a bit beyond me.

See if this article helps.

http://xldynamic.com/source/xld.SUMPRODUCT.html

**snapfade** · 06-25-2018, 09:41 PM

When I copied the formula from Jan to Feb through Dec, "[Odds] was treated as relative. I changed the reference to [[Odds]:[Odds]], thereby making Odds absolute. That seems to have done the trick.

Thanks, Flame for the link; I will take a close look.

And special thanks to bebo for doing the heavy lifting.

This forum is truly awesome.

**FlameRetired** · 06-25-2018, 10:08 PM

You bet, and you're right. bebo did the heavy lifting.

Thanks for the feedback, rep and marking your thread Solved.

**snapfade** · 07-11-2018, 03:46 PM

The formula presented by Bebo works perfectly:

Please Login or Register  to view this content.

Can anybody explain to me how it works?

Thanks,
tom

**bebo021999** · 07-13-2018, 10:58 PM

Originally Posted by snapfade

Can anybody explain to me how it works?

Nice to hear from you.
Working in cell C2, sheet Summary, we have conditions:
Untitled1.png

SUMPRODUCT work like this:
Step1: (ConditionRange1*ConditionRange2*...COnditionRangeN)*(ValueRange1*...*ValueRangeN) for each row
Step2: Sum all values found from all rows

Untitled1.png

In which,
ConditionRange1: INDIRECT("tblData["&$A2&"]")
ConditionRange2: (tblData[[#Headers],[Jan]:[Dec]]=C$1)* tblData[[Jan]:[Dec]]
ValueRange1:$B2
ValueRange2: tblData[Odds]

SUMPRODUCT will go thru each row, multifly each row, then sum
row 1: 95%*0*..*50% =0
row 2: 75%*0*..*50% =0
row 3: 100%*23739*..*50%=11689
...
row n
........................

Finally:
=SUMPRODUCT(tblData[Odds] * INDIRECT("tblData["&$A2&"]") * (tblData[[#Headers],[Jan]:[Dec]]=C$1) * tblData[[Jan]:[Dec]] * $B2)

Hope this helps.

Summarize Data by Person, Month and Percentages

LinkBack

Thread Tools

Rate This Thread

Display

Summarize Data by Person, Month and Percentages

Re: Summarize Data by Person, Month and Percentages

Re: Summarize Data by Person, Month and Percentages

Re: Summarize Data by Person, Month and Percentages

Re: Summarize Data by Person, Month and Percentages

Re: Summarize Data by Person, Month and Percentages

Re: Summarize Data by Person, Month and Percentages

Re: Summarize Data by Person, Month and Percentages

Re: Summarize Data by Person, Month and Percentages

Re: Summarize Data by Person, Month and Percentages

Re: Summarize Data by Person, Month and Percentages

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Summarize data into occurrences per month

Charts to show percentages for each person

Summarize Data by Month by Person

[SOLVED] Trying to Pull Data by keyword for each month and use for percentages for each month (Y/N)

[SOLVED] Trying to Pull Data by keyword for each month and use for percentages for each month (Y/N)

Rows of records, each w/ start & end date. Need to compile and summarize month by month

Best way to summarize into month

Bookmarks

Bookmarks

Posting Permissions