Use of INDEX with or without MATCH function or Lookup function

**GStone** · 06-06-2018, 03:51 PM

I'm trying to count and sum information from 3 columns that fall under a date. I want to be able to add or change dates to the summary page without having to change column references and wonder if there is an easy way to use an INDEX formula with or without using MATCH inside the INDEX formula. The attached spreadsheet was created by someone else and I believe there may be a better way to count/sum data. The Dates on the Data worksheet were originally in merged cells (e.g. 4/23/2018 was originally merged in B1:D1, but I changed that to see if it would be easier to use a formula)

**mrice** · 06-06-2018, 04:22 PM

Try

Please Login or Register  to view this content.

in C3 on the summary sheet.

**CAntosh** · 06-06-2018, 04:36 PM

Alternatively, you can use SUMPRODUCT. Put a helper row (that you can hide) in column C with the key phrase and it gets even easier.

In D3: =SUMPRODUCT((Data!$B$3:$G$99=$C3)*(Data!$B$1:$G$1=D$2))

In D4: =SUMPRODUCT((Data!$B$3:$G$99=$C3)*(Data!$B$1:$G$1=D$2),Data!$A$3:$F$99)

Fill right for other dates. Copy the set down for other categories. See attached:

**GStone** · 06-06-2018, 09:16 PM

Thanks. This works. I do support my local American Cancer Society.

**GStone** · 06-06-2018, 09:20 PM

Thank you for this solution. I'm going to let the author of the workbook decide which solution to use. I appreciate your help. I'm posting a joke or anecdote to the joke thread you provided. The anecdote I'm thinking of may be better than any joke I could share.