Conversation How to SUM Data from non-regular rows in Excel
https://techcommunity.microsoft.com/t5/excel/how-to-sum-data-from-non-regular-rows/m-p/899897#M41621
<P>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):</P><TABLE border="1"><TBODY><TR><TD> </TD><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>Total</TD></TR><TR><TD>XA</TD><TD>Y</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>1</TD><TD>0</TD><TD>1</TD></TR><TR><TD>XB</TD><TD>Y</TD><TD>0</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>1</TD></TR><TR><TD>XC</TD><TD>Y</TD><TD>0</TD><TD>2</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>2</TD></TR><TR><TD><P>XD</P></TD><TD>N</TD><TD>0</TD><TD>0</TD><TD>1</TD><TD>1</TD><TD>0</TD><TD>2</TD></TR><TR><TD>XA</TD><TD>Y</TD><TD>0</TD><TD>1</TD><TD>0</TD><TD>2</TD><TD>0</TD><TD>3</TD></TR><TR><TD>XC</TD><TD>Y</TD><TD>0</TD><TD>1</TD><TD>1</TD><TD>2</TD><TD>0</TD><TD>4</TD></TR><TR><TD>XD</TD><TD>N</TD><TD>1</TD><TD>1</TD><TD>0</TD><TD>2</TD><TD>0</TD><TD>4</TD></TR><TR><TD>XB</TD><TD>N</TD><TD>0</TD><TD>0</TD><TD>1</TD><TD>2</TD><TD>0</TD><TD>3</TD></TR><TR><TD>XC</TD><TD>N</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>1</TD></TR><TR><TD>XD</TD><TD>N</TD><TD>0</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD><P>1</P></TD></TR></TBODY></TABLE><P> </P><P>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. </P><P>It all must be done with functions as I am working with around 50 columns and endless number of rows. </P><P>I appreciate all help such as ready function as well as ideas (including VBA). </P><P>Thanks</P>Tue, 08 Oct 2019 15:59:32 GMTAndy_Przybysz2019-10-08T15:59:32ZHow to SUM Data from non-regular rows
https://techcommunity.microsoft.com/t5/excel/how-to-sum-data-from-non-regular-rows/m-p/899897#M41621
<P>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):</P><TABLE border="1"><TBODY><TR><TD> </TD><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>Total</TD></TR><TR><TD>XA</TD><TD>Y</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>1</TD><TD>0</TD><TD>1</TD></TR><TR><TD>XB</TD><TD>Y</TD><TD>0</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>1</TD></TR><TR><TD>XC</TD><TD>Y</TD><TD>0</TD><TD>2</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>2</TD></TR><TR><TD><P>XD</P></TD><TD>N</TD><TD>0</TD><TD>0</TD><TD>1</TD><TD>1</TD><TD>0</TD><TD>2</TD></TR><TR><TD>XA</TD><TD>Y</TD><TD>0</TD><TD>1</TD><TD>0</TD><TD>2</TD><TD>0</TD><TD>3</TD></TR><TR><TD>XC</TD><TD>Y</TD><TD>0</TD><TD>1</TD><TD>1</TD><TD>2</TD><TD>0</TD><TD>4</TD></TR><TR><TD>XD</TD><TD>N</TD><TD>1</TD><TD>1</TD><TD>0</TD><TD>2</TD><TD>0</TD><TD>4</TD></TR><TR><TD>XB</TD><TD>N</TD><TD>0</TD><TD>0</TD><TD>1</TD><TD>2</TD><TD>0</TD><TD>3</TD></TR><TR><TD>XC</TD><TD>N</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>1</TD></TR><TR><TD>XD</TD><TD>N</TD><TD>0</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD><P>1</P></TD></TR></TBODY></TABLE><P> </P><P>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. </P><P>It all must be done with functions as I am working with around 50 columns and endless number of rows. </P><P>I appreciate all help such as ready function as well as ideas (including VBA). </P><P>Thanks</P>Tue, 08 Oct 2019 15:59:32 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-sum-data-from-non-regular-rows/m-p/899897#M41621Andy_Przybysz2019-10-08T15:59:32ZRe: How to SUM Data from non-regular rows
https://techcommunity.microsoft.com/t5/excel/how-to-sum-data-from-non-regular-rows/m-p/901120#M41670
<P><LI-USER uid="422213"></LI-USER> </P><P>In the attached file, the formula in C15 is: </P><P>=SUMPRODUCT(C$2:C$11,<BR />($A$2:$A$11=$A15)*<BR />($B$2:$B$11=$B15))</P><P>Please confirm if the foregoing formula returns your desired results.</P>Wed, 09 Oct 2019 05:12:19 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-sum-data-from-non-regular-rows/m-p/901120#M41670Twifoo2019-10-09T05:12:19Z