Index/Match formula or other method?

ac7

New Member
Joined
Jul 26, 2023
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hello -

I am unsure of the best way to accomplish what I am looking for. I have 2 worksheets, 1 labeled as 'Data' and the other as 'Results'. Here is the info on the 'Data' worksheet:

A​
B​
C​
D​
E​
F​
1​
Box100101102103104
2​
Date Completed9/1/20239/15/202310/5/202310/12/202311/3/2023
3​
Option 111111
4​
Option 211
5​
Option 3111
6​
Option 41

On the 'Results' worksheet, I need to find the matching option number on the 'Data' worksheet and then sum the number of times that option was used for a particular month.

A​
B​
C​
D​
1​
SeptOctNov
2​
Option 1221
3​
Option 2110
4​
Option 3111
5​
Option 4100

I am struggling with the best way to do this. Any help would be appreciated.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Data sheet:
Dante Amor
ABCDEF
1Box100101102103104
2Date Completed01/sep/202315/sep/202301/oct/202315/oct/202303/nov/2023
3Option 111111
4Option 211
5Option 3111
6Option 41
Data


Consider the following:
In row 1 of the result sheet, you must put 3 letters of each month as shown in the following example:
Result sheet:
Dante Amor
ABCD
1SepOctNov
2Option 1221
3Option 2110
4Option 3111
5Option 4100
Results
Cell Formulas
RangeFormula
B2:D5B2=SUMPRODUCT((Data!$A$3:$A$6=$A2)*(TEXT(Data!$B$2:$F$2,"mmm")=LOWER(B$1))*(Data!$B$3:$F$6))



----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --
 
Upvote 1
Solution
Given that you have excel 365, another approach could be this (using the same layout as Dante)

ac7.xlsm
ABCD
1SepOctNov
2Option 1221
3Option 2110
4Option 3111
5Option 4100
Results
Cell Formulas
RangeFormula
B2:D5B2=SUM(FILTER(FILTER(Data!$B$3:$F$6,Data!$A$3:$A$6=$A2),TEXT(Data!$B$2:$F$2,"mmm")=B$1))



BTW, there is no need for the LOWER function in Dante's suggestion. This would work just as well.
Excel Formula:
=SUMPRODUCT((Data!$A$3:$A$6=$A2)*(TEXT(Data!$B$2:$F$2,"mmm")=B$1)*(Data!$B$3:$F$6))
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top