Home

How to SUM Data from non-regular rows

%3CLINGO-SUB%20id%3D%22lingo-sub-899897%22%20slang%3D%22en-US%22%3EHow%20to%20SUM%20Data%20from%20non-regular%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-899897%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20collecting%20data%20from%20source%20which%20omits%20categories%20with%20zero%20values.%20Therefore%2C%20number%20of%20rows%20will%20never%20be%20the%20same.%20Columns%20will%20always%20remain%20the%20same.%20I%20need%20to%20create%20an%20annual%20report%20based%20on%20monthly%20(or%20even%20daily)%20reports.%20The%20data%20will%20come%20like%20this%20(these%20are%203%20separate%20reports%20combined%20already%2C%20one%20with%20XA-XD%20range%2C%20one%20without%20XB%20and%20one%20without%20XA%2C%20also%20some%20of%20them%20are%20with%20conditional%20value%20of%20Y%20or%20N)%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3ETotal%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EXA%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EXB%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EXC%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EXD%3C%2FP%3E%3C%2FTD%3E%3CTD%3EN%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EXA%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EXC%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EXD%3C%2FTD%3E%3CTD%3EN%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EXB%3C%2FTD%3E%3CTD%3EN%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EXC%3C%2FTD%3E%3CTD%3EN%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EXD%3C%2FTD%3E%3CTD%3EN%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%3CP%3E1%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20end%20I%20need%20report%20that%20contains%20summary%20for%20all%20rows%20with%20for%20each%20condition%20(IE%20XA%20with%20Y%20and%20XA%20with%20N)%20and%20then%20the%20same%20but%20for%20each%20condition%20in%20columns%20A%20to%20E.%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20all%20must%20be%20done%20with%20functions%20as%20I%20am%20working%20with%20around%2050%20columns%20and%20endless%20number%20of%20rows.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20all%20help%20such%20as%20ready%20function%20as%20well%20as%20ideas%20(including%20VBA).%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-899897%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Emultiple%20accounts%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ERows%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-901120%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20SUM%20Data%20from%20non-regular%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-901120%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422213%22%20target%3D%22_blank%22%3E%40Andy_Przybysz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20the%20formula%20in%20C15%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(C%242%3AC%2411%2C%3CBR%20%2F%3E(%24A%242%3A%24A%2411%3D%24A15)*%3CBR%20%2F%3E(%24B%242%3A%24B%2411%3D%24B15))%3C%2FP%3E%3CP%3EPlease%20confirm%20if%20the%20foregoing%20formula%20returns%20your%20desired%20results.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Andy_Przybysz
Occasional Visitor

I am collecting data from source which omits categories with zero values. Therefore, number of rows will never be the same. Columns will always remain the same. I need to create an annual report based on monthly (or even daily) reports. The data will come like this (these are 3 separate reports combined already, one with XA-XD range, one without XB and one without XA, also some of them are with conditional value of Y or N):

  ABCDETotal
XAY000101
XBY010001
XCY020002

XD

N001102
XAY010203
XCY011204
XDN110204
XBN001203
XCN100001
XDN01000

1

 

In the end I need report that contains summary for all rows with for each condition (IE XA with Y and XA with N) and then the same but for each condition in columns A to E. 

It all must be done with functions as I am working with around 50 columns and endless number of rows. 

I appreciate all help such as ready function as well as ideas (including VBA). 

Thanks

1 Reply

@Andy_Przybysz 

In the attached file, the formula in C15 is: 

=SUMPRODUCT(C$2:C$11,
($A$2:$A$11=$A15)*
($B$2:$B$11=$B15))

Please confirm if the foregoing formula returns your desired results.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies