info
Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Complex Sumproduct formula
17 views
Skip to first unread message
Ray Smart
Feb 2, 2021, 12:08:59 AM2/2/21
to
Hi Guys,
Hoping to leverage off all your excellent skills...
I am trying to figure out how to do the following:
Sheet1 - Breakdown of item by type
Col-A,B,C,D,E
Row
1-Item, Total, Fee, Other, Allowables
2-1, 45293, 21745,20610,2938
3-2, 45612, 30287, 12366, 2959
4-3,94517, 68329, 119732, 6455
Sheet2 - Breakdown of item by month
Col-A,B,C,D,E
Row
1-Item, Nov, Dec, Jan, Feb, Mar
2-1, 8569, 36724, 0, 0, 0
3-2, 0, 12583, 33030, 0, 0
4-3,2508, 38869, 38869, 35107, 2508
I need to understand what the revenue by type by month is and I am not getting the sumproduct to work.
Any pointers?
Hoping to leverage off all your excellent skills...
I am trying to figure out how to do the following:
Sheet1 - Breakdown of item by type
Col-A,B,C,D,E
Row
1-Item, Total, Fee, Other, Allowables
2-1, 45293, 21745,20610,2938
3-2, 45612, 30287, 12366, 2959
4-3,94517, 68329, 119732, 6455
Sheet2 - Breakdown of item by month
Col-A,B,C,D,E
Row
1-Item, Nov, Dec, Jan, Feb, Mar
2-1, 8569, 36724, 0, 0, 0
3-2, 0, 12583, 33030, 0, 0
4-3,2508, 38869, 38869, 35107, 2508
I need to understand what the revenue by type by month is and I am not getting the sumproduct to work.
Any pointers?
Claus Busch
Feb 2, 2021, 2:53:29 PM2/2/21
to
Hi Ray,
Am Mon, 1 Feb 2021 21:08:57 -0800 (PST) schrieb Ray Smart:
> I am trying to figure out how to do the following:
>
> Sheet1 - Breakdown of item by type
> Col-A,B,C,D,E
> Row
> 1-Item, Total, Fee, Other, Allowables
> 2-1, 45293, 21745,20610,2938
> 3-2, 45612, 30287, 12366, 2959
> 4-3,94517, 68329, 119732, 6455
>
> Sheet2 - Breakdown of item by month
> Col-A,B,C,D,E
> Row
> 1-Item, Nov, Dec, Jan, Feb, Mar
> 2-1, 8569, 36724, 0, 0, 0
> 3-2, 0, 12583, 33030, 0, 0
> 4-3,2508, 38869, 38869, 35107, 2508
try:
=SUMPRODUCT((Sheet2!$A$2:$A$10=A2)*Sheet2!$B$2:$F$10)
or
=SUM(INDEX(Sheet2!$B$2:$F$10,MATCH(A2,Sheet2!$A$2:$A$10,0),))
Regards
Claus B.
--
Windows10
Microsoft 365 for business
Am Mon, 1 Feb 2021 21:08:57 -0800 (PST) schrieb Ray Smart:
> I am trying to figure out how to do the following:
>
> Sheet1 - Breakdown of item by type
> Col-A,B,C,D,E
> Row
> 1-Item, Total, Fee, Other, Allowables
> 2-1, 45293, 21745,20610,2938
> 3-2, 45612, 30287, 12366, 2959
> 4-3,94517, 68329, 119732, 6455
>
> Sheet2 - Breakdown of item by month
> Col-A,B,C,D,E
> Row
> 1-Item, Nov, Dec, Jan, Feb, Mar
> 2-1, 8569, 36724, 0, 0, 0
> 3-2, 0, 12583, 33030, 0, 0
> 4-3,2508, 38869, 38869, 35107, 2508
=SUMPRODUCT((Sheet2!$A$2:$A$10=A2)*Sheet2!$B$2:$F$10)
or
=SUM(INDEX(Sheet2!$B$2:$F$10,MATCH(A2,Sheet2!$A$2:$A$10,0),))
Regards
Claus B.
--
Windows10
Microsoft 365 for business
Ray Smart
Feb 3, 2021, 2:45:54 AM2/3/21
to
Thanks for feedback, however I already have the information that your formulas are giving me.
I need to understand for the month of November, what my total amounts are per ItemType. Ie... In November I have a total of 11,077 across all itemTypes - but how is the 11,077 broken down across Fee, Other and Allowables given the mix in sheet 1?
Effectively, my new table will look like:
itemtype, Nov, Dec, Jan, etc
Fee,?,?,?
Other,?,?,?
Allowable,?,?,?
Total,11077,60021, etc
Does this make sense?
Claus Busch
Feb 3, 2021, 6:02:52 AM2/3/21
to
Hi Ray,
Am Tue, 2 Feb 2021 23:45:53 -0800 (PST) schrieb Ray Smart:
> Effectively, my new table will look like:
> itemtype, Nov, Dec, Jan, etc
> Fee,?,?,?
> Other,?,?,?
> Allowable,?,?,?
> Total,11077,60021, etc
have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgfAFx-eJlG5H2S1VOw?e=ehpuk8
Is that the expected result?
Am Tue, 2 Feb 2021 23:45:53 -0800 (PST) schrieb Ray Smart:
> Effectively, my new table will look like:
> itemtype, Nov, Dec, Jan, etc
> Fee,?,?,?
> Other,?,?,?
> Allowable,?,?,?
> Total,11077,60021, etc
https://1drv.ms/x/s!AqMiGBK2qniTgfAFx-eJlG5H2S1VOw?e=ehpuk8
Is that the expected result?
0 new messages