Hi Friends,
I'm creating a report-generator that is based on data in a sheet that has 50+ columns. My end users are going to want to see various versions of this report using the values in 4 of the columns as selection criteria. For each of the 4 columns in question, they will want to select either an individual value as the criteria OR not use that column to select on. I need to calculate sums, counts, etc. for some of the other columns on the basis of the selection criteria in the 4 key columns.
Please see the attached example file. I'm using drop-down data validation (orange cells) for the users to select values for the 4 key columns, and also allowing 'All' as a choice for each to not use that particular column to select on. In the example, I'm looking for a sum of the 'Cost' column for all records that meet the criteria the user selects.
I have no problem when the selection criteria are each one of the values in the 4 key columns. I'm hamstrung, however, if any of the criteria are excluded (user selects 'All'). Can you help me with a formula that takes 'All' into account? Thanks in advance for your help!
I'm creating a report-generator that is based on data in a sheet that has 50+ columns. My end users are going to want to see various versions of this report using the values in 4 of the columns as selection criteria. For each of the 4 columns in question, they will want to select either an individual value as the criteria OR not use that column to select on. I need to calculate sums, counts, etc. for some of the other columns on the basis of the selection criteria in the 4 key columns.
Please see the attached example file. I'm using drop-down data validation (orange cells) for the users to select values for the 4 key columns, and also allowing 'All' as a choice for each to not use that particular column to select on. In the example, I'm looking for a sum of the 'Cost' column for all records that meet the criteria the user selects.
I have no problem when the selection criteria are each one of the values in the 4 key columns. I'm hamstrung, however, if any of the criteria are excluded (user selects 'All'). Can you help me with a formula that takes 'All' into account? Thanks in advance for your help!