• 670K Members
• 10.4K Online
• 827K Conversations

## How to SUM Data from non-regular rows

Highlighted
Occasional Visitor

# How to SUM Data from non-regular rows

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):

 A B C D E Total XA Y 0 0 0 1 0 1 XB Y 0 1 0 0 0 1 XC Y 0 2 0 0 0 2 XD N 0 0 1 1 0 2 XA Y 0 1 0 2 0 3 XC Y 0 1 1 2 0 4 XD N 1 1 0 2 0 4 XB N 0 0 1 2 0 3 XC N 1 0 0 0 0 1 XD N 0 1 0 0 0 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

# Re: How to SUM Data from non-regular rows

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.