Complex Sumproduct formula
From
Ray Smart@21:1/5 to
All on Mon Feb 1 21:08:57 2021
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?
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
On Tuesday, 2 February 2021 at 23:53:29 UTC+4,
[email protected] wrote:
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
Hi Claus,
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?
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)