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
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies