Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 2nd 21, 05:08 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2021
Posts: 2
Default Complex Sumproduct formula

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?

  #2   Report Post  
Old February 2nd 21, 07:53 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,866
Default Complex Sumproduct formula

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
  #3   Report Post  
Old February 3rd 21, 07:45 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2021
Posts: 2
Default Complex Sumproduct formula

On Tuesday, 2 February 2021 at 23:53:29 UTC+4, 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?
  #4   Report Post  
Old February 3rd 21, 11:02 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,866
Default Complex Sumproduct formula

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!AqMiGBK2qniTgfAF...S1VOw?e=ehpuk8
Is that the expected result?


Regards
Claus B.
--
Windows10
Microsoft 365 for business


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
complex color fill conditions- if statements or complex formula? lilly8008 Excel Discussion (Misc queries) 1 December 18th 09 04:57 AM
Sumproduct forumla for complex sum. DocBrown Excel Programming 18 September 19th 09 02:03 AM
Sumproduct syntax - more complex Mitchell Excel Discussion (Misc queries) 4 November 9th 06 07:46 PM
a complex use of sumproduct ? pimar[_9_] Excel Programming 2 February 24th 06 11:39 AM
Help with Complex SUMPRODUCT formula Murph Excel Worksheet Functions 5 January 26th 05 02:40 PM


All times are GMT +1. The time now is 10:19 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017