# Sumproduct + subtotal + search / +date

#### Barbatian

##### New Member
Hello everyone,

I have a data in 3 columns:
Event NR | Event Type | Date

Event type may be: A,B,C,A+B,A+C

I don't need the result for Event Type A+B but I need them to be separately calculated respectively to A and B Event types:
e.g. A+B Event Should be calculated as 1 Event A and 1 Event B

I have a formula, which allows me to count so but I have to filter the date in other colums to see the result for specific period (e.g. month, year)

=SUMPRODUCT(SUBTOTAL(3;OFFSET(Input!B9:B10193;ROW(Input!B9:B10193)-MIN(ROW(Input!B9:B10193));;1));ISNUMBER(SEARCH("Event A";Input!B9:B10193))+0)

I would like to have a general overview at once for every month/year in e.g. separte sheet.

How Can I modify this formula to also take the date into consideration - I would create a formula for each month/year.
This would also allow me to have an updated overview in case of new "records" for the past.

I also tried using pivot table, however I couldn't manage to separte A+B Events to count separately into A and B Event types.
Pivot table would be perfect if I could somehow separte these calculations.

There is a solution to spread the data source so there is no A+B event but two events instead but because of interpretation we cannot do so.

I would be really thankful for advices and solution.

Kind regards,
Michal​

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### Andrew Poulsom

##### MrExcel MVP
Welcome to MrExcel.

Please post some actual data rather than an image so that it can be copied and pasted into Excel. To test the date you can add a condition like:

--(TEXT(Input!C9:C10193,"mmm-yyyy")="Sep 2014")

where Sep 2014 can also be derived from a cell that contains the date, eg TEXT(A1,"mmm-yyyy").

#### Barbatian

##### New Member
Welcome to MrExcel.

Please post some actual data rather than an image so that it can be copied and pasted into Excel. To test the date you can add a condition like:

--(TEXT(Input!C9:C10193,"mmm-yyyy")="Sep 2014")

where Sep 2014 can also be derived from a cell that contains the date, eg TEXT(A1,"mmm-yyyy").

Hello,

Sorry but I'm a total begginer. I don't know what to do with your answer. Would you be able to merge my formula with yours?

I hope this example will help. January

 ISIN Event Sending date 1 A 03.01.2014 2 B 03.01.2014 3 B 03.01.2014 4 A+B 03.01.2014 5 A+B 03.01.2014 6 C 03.01.2014 7 A+C 03.01.2014 8 B+C 03.01.2014 9 A 03.01.2014 10 A 06.01.2014

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

Kind regards,
Michal

#### Andrew Poulsom

##### MrExcel MVP
Maybe you can adapt this example (formula in F2 copied down and across):

Excel 2010
ABCDEFG
1ISINEventSending date01/01/201401/02/2014
21A03 Jan 2014A42
32B03 Jan 2014B32
43B03 Jan 2014C21
54A+B03 Jan 2014
65A+B03 Feb 2014
76C03 Jan 2014
87A+C03 Jan 2014
98B+C03 Feb 2014
109A03 Feb 2014
1110A06 Jan 2014
Sheet1
Cell Formulas
RangeFormula
F2=SUMPRODUCT(--(ISNUMBER(SEARCH(\$E2,\$B\$2:\$B\$11))),--(TEXT(\$C\$2:\$C\$11,"mmm yyyy")=TEXT(F\$1,"mmm yyyy")))

#### Barbatian

##### New Member
How Can I change the formula, so I can have such Month Format. I would like to also have a year filter (year cell should be marged along the months):

Excel 2010
A
B
C
D
E
F
G
20142014
1
ISIN
Event
Sending date
January
February
2
1
A
03 Jan 2014
A
4
2
3
2
B
03 Jan 2014
B
3
2
4
3
B
03 Jan 2014
C
2
1
5
4
A+B
03 Jan 2014
6
5
A+B
03 Feb 2014
7
6
C
03 Jan 2014
8
7
A+C
03 Jan 2014
9
8
B+C
03 Feb 2014
10
9
A
03 Feb 2014
11
10
A
06 Jan 2014

<TBODY>
</TBODY>

I work on 2014 and instead of "," I should have ";". I can't figure out the "date" formatting in the formula.
mmm yyyy = What does it exacly mean? - How Can I search for F1 (mmm format) in C2-C11 (dd.mm.yyyy format)

Kind regards
Michal

#### Andrew Poulsom

##### MrExcel MVP
The format "mmm yyyy" mean first three characters of the month concatenated with a space and the year, eg "Jan 2014". See if you can adapt this updated example:

Excel 2010
ABCDEFG
1ISINEventSending date20142014
21A03-Jan-14JanuaryFebruary
32B03-Jan-14A42
43B03-Jan-14B32
54A+B03-Jan-14C21
65A+B03-Feb-14
76C03-Jan-14
87A+C03-Jan-14
98B+C03-Feb-14
109A03-Feb-14
1110A06-Jan-14
Sheet1
Cell Formulas
RangeFormula
F3=SUMPRODUCT(--(ISNUMBER(SEARCH(\$E3,\$B\$2:\$B\$11))),--(TEXT(\$C\$2:\$C\$11,"mmm yyyy")=TEXT(F\$2&"-"&F\$1,"mmm yyyy")))

#### Barbatian

##### New Member
This is not working at all. However:

F2 counts me 6, which is a total of A event in year 2014. How Can I count it to January? I don't understand the formatting. When I cahnge mmm to anything else I count zero

 ISIN Event Sending date 2014 2014 1 A 03.01.2014 January February 2 B 03.01.2014 A 6 3 B 03.01.2014 B 4 A+B 03.01.2014 C 5 A+B 03.02.2014 6 C 03.01.2014 7 A+C 03.01.2014 8 B+C 03.02.2014 9 A 03.02.2014 10 A 06.01.2014

<TBODY>
</TBODY>

Worksheet Formulas
Cell
Formula
F2
=SUMPRODUCT(--(ISNUMBER(SEARCH(\$D3;\$B\$2:\$B\$11)));--(TEXT(\$C\$2:\$C\$11;"mmm")=TEXT(E\$1;"mmm")))

<TBODY>
</TBODY>

Please look at print screen. - F2=F3 on screen.

#### Barbatian

##### New Member
PS I change the Language in Control Panel to English US and this formula worked like a charm:

It seems like formatting is german but the date cells are formated to dd.mm.yyyy. If I change formatting it doesn't work.

=SUMPRODUCT(--(ISNUMBER(SEARCH(\$D\$3,Input!\$B\$9:\$B\$10000))),--(TEXT(Input!\$C\$9:\$C\$10000,"mm.jjjj")=TEXT(E\$2&"-"&E\$1,"mm.jjjj")))

Do you have any idea?

I will prepare final version I will give feedback.
Thank you so much for this moment

Kind regards,
Michal

#### Andrew Poulsom

##### MrExcel MVP
If you are in Germany you need to replace commas with semicolons.

#### Barbatian

##### New Member
I know the problem.

The dates are in format dd.mm.jjjj, which is German, even though they are displayed in other formatting, however the table has Months names in english. Can I somehow combine it?

Last edited:

Replies
2
Views
211
Replies
4
Views
129
Replies
2
Views
251
Replies
2
Views
251
Replies
0
Views
191

Threads
1,190,957
Messages
5,983,835
Members
439,864
Latest member
qazxsw12

### 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

### 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