Sumproduct + subtotal + search / +date

[Barbatian]

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​

 

[Andrew Poulsom]

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]

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</SPAN>	Event</SPAN>	Sending date</SPAN>
1</SPAN>	A</SPAN>	03.01.2014</SPAN>
2</SPAN>	B</SPAN>	03.01.2014</SPAN>
3</SPAN>	B</SPAN>	03.01.2014</SPAN>
4</SPAN>	A+B</SPAN>	03.01.2014</SPAN>
5</SPAN>	A+B</SPAN>	03.01.2014</SPAN>
6</SPAN>	C</SPAN>	03.01.2014</SPAN>
7</SPAN>	A+C</SPAN>	03.01.2014</SPAN>
8</SPAN>	B+C</SPAN>	03.01.2014</SPAN>
9</SPAN>	A</SPAN>	03.01.2014</SPAN>
10</SPAN>	A</SPAN>	06.01.2014</SPAN>

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

Kind regards,
Michal

 

[Andrew Poulsom]

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

Excel 2010
	A	B	C	D	E	F	G
1	ISIN	Event	Sending date			01/01/2014	01/02/2014
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
Sheet1

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(--(ISNUMBER(SEARCH($E2,$B$2:$B$11))),--(TEXT($C$2:$C$11,"mmm yyyy")=TEXT(F$1,"mmm yyyy")))

 

[Barbatian]

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
						2014	2014
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]

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
	A	B	C	D	E	F	G
1	ISIN	Event	Sending date			2014	2014
2	1	A	03-Jan-14			January	February
3	2	B	03-Jan-14		A	4	2
4	3	B	03-Jan-14		B	3	2
5	4	A+B	03-Jan-14		C	2	1
6	5	A+B	03-Feb-14
7	6	C	03-Jan-14
8	7	A+C	03-Jan-14
9	8	B+C	03-Feb-14
10	9	A	03-Feb-14
11	10	A	06-Jan-14
Sheet1

Cell Formulas
Range	Formula
F3	=SUMPRODUCT(--(ISNUMBER(SEARCH($E3,$B$2:$B$11))),--(TEXT($C$2:$C$11,"mmm yyyy")=TEXT(F$2&"-"&F$1,"mmm yyyy")))

 

[Barbatian]

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]

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]

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

 

[Barbatian]

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?