Oct 08 2019 08:59 AM
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
Oct 08 2019 10:12 PM
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.