Sumproduct + subtotal + search / +date

Barbatian

New Member
Joined
Sep 12, 2014
Messages
25
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)

2dhcn04.png


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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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").
 
Upvote 0
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

[TABLE="width: 379"]
<TBODY>[TR]
[TD]ISIN</SPAN>[/TD]
[TD]Event</SPAN>[/TD]
[TD]Sending date</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD="align: right"]03.01.2014</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD="align: right"]03.01.2014</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD="align: right"]03.01.2014</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]4</SPAN>[/TD]
[TD]A+B</SPAN>[/TD]
[TD="align: right"]03.01.2014</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5</SPAN>[/TD]
[TD]A+B</SPAN>[/TD]
[TD="align: right"]03.01.2014</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]6</SPAN>[/TD]
[TD]C</SPAN>[/TD]
[TD="align: right"]03.01.2014</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]7</SPAN>[/TD]
[TD]A+C</SPAN>[/TD]
[TD="align: right"]03.01.2014</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]8</SPAN>[/TD]
[TD]B+C</SPAN>[/TD]
[TD="align: right"]03.01.2014</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]9</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD="align: right"]03.01.2014</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]10</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD="align: right"]06.01.2014</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

Kind regards,
Michal
 
Upvote 0
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")))
 
Upvote 0
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
1
ISIN
Event
Sending date
2
A
A
3
B
B
4
B
C
5
A+B
6
A+B
7
C
8
A+C
9
B+C
10
A
11
A

<TBODY>
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2014[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] January
[/TD]
[TD="align: right"] February
[/TD]

[TD="align: right"]1
[/TD]

[TD="align: right"]03 Jan 2014
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: right"]2
[/TD]

[TD="align: right"]03 Jan 2014
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: right"]3
[/TD]

[TD="align: right"]03 Jan 2014
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: right"]4
[/TD]

[TD="align: right"]03 Jan 2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5
[/TD]

[TD="align: right"]03 Feb 2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6
[/TD]

[TD="align: right"]03 Jan 2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7
[/TD]

[TD="align: right"]03 Jan 2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]8
[/TD]

[TD="align: right"]03 Feb 2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9
[/TD]

[TD="align: right"]03 Feb 2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10
[/TD]

[TD="align: right"]06 Jan 2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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
 
Upvote 0
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")))
 
Upvote 0
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

[TABLE="width: 434"]
<TBODY>[TR]
[TD="class: xl88, width: 88, bgcolor: white"]ISIN
[/TD]
[TD="class: xl88, width: 88, bgcolor: white"]Event
[/TD]
[TD="class: xl88, width: 98, bgcolor: white"]Sending date
[/TD]
[TD="class: xl89, width: 98, bgcolor: white"][/TD]
[TD="class: xl89, width: 101, bgcolor: white"]2014
[/TD]
[TD="class: xl89, width: 104, bgcolor: white"]2014
[/TD]
[/TR]
[TR]
[TD="class: xl89, width: 88, bgcolor: white"]1
[/TD]
[TD="class: xl88, width: 88, bgcolor: white"]A
[/TD]
[TD="class: xl90, width: 98, bgcolor: white"]03.01.2014
[/TD]
[TD="class: xl89, width: 98, bgcolor: white"][/TD]
[TD="class: xl88, width: 101, bgcolor: white"]January
[/TD]
[TD="class: xl88, width: 104, bgcolor: white"]February
[/TD]
[/TR]
[TR]
[TD="class: xl89, width: 88, bgcolor: white"]2
[/TD]
[TD="class: xl88, width: 88, bgcolor: white"]B
[/TD]
[TD="class: xl90, width: 98, bgcolor: white"]03.01.2014
[/TD]
[TD="class: xl88, width: 98, bgcolor: white"]A
[/TD]
[TD="class: xl91, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl91, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl89, width: 88, bgcolor: white"]3
[/TD]
[TD="class: xl88, width: 88, bgcolor: white"]B
[/TD]
[TD="class: xl90, width: 98, bgcolor: white"]03.01.2014
[/TD]
[TD="class: xl88, width: 98, bgcolor: white"]B
[/TD]
[TD="class: xl91, bgcolor: transparent, align: right"][/TD]
[TD="class: xl91, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl89, width: 88, bgcolor: white"]4
[/TD]
[TD="class: xl88, width: 88, bgcolor: white"]A+B
[/TD]
[TD="class: xl90, width: 98, bgcolor: white"]03.01.2014
[/TD]
[TD="class: xl88, width: 98, bgcolor: white"]C
[/TD]
[TD="class: xl91, bgcolor: transparent"][/TD]
[TD="class: xl91, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl89, width: 88, bgcolor: white"]5
[/TD]
[TD="class: xl88, width: 88, bgcolor: white"]A+B
[/TD]
[TD="class: xl90, width: 98, bgcolor: white"]03.02.2014
[/TD]
[TD="class: xl89, width: 98, bgcolor: white"][/TD]
[TD="class: xl89, width: 101, bgcolor: white"][/TD]
[TD="class: xl89, width: 104, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl89, width: 88, bgcolor: white"]6
[/TD]
[TD="class: xl88, width: 88, bgcolor: white"]C
[/TD]
[TD="class: xl90, width: 98, bgcolor: white"]03.01.2014
[/TD]
[TD="class: xl89, width: 98, bgcolor: white"][/TD]
[TD="class: xl89, width: 101, bgcolor: white"][/TD]
[TD="class: xl89, width: 104, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl89, width: 88, bgcolor: white"]7
[/TD]
[TD="class: xl88, width: 88, bgcolor: white"]A+C
[/TD]
[TD="class: xl90, width: 98, bgcolor: white"]03.01.2014
[/TD]
[TD="class: xl89, width: 98, bgcolor: white"][/TD]
[TD="class: xl89, width: 101, bgcolor: white"][/TD]
[TD="class: xl89, width: 104, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl89, width: 88, bgcolor: white"]8
[/TD]
[TD="class: xl88, width: 88, bgcolor: white"]B+C
[/TD]
[TD="class: xl90, width: 98, bgcolor: white"]03.02.2014
[/TD]
[TD="class: xl89, width: 98, bgcolor: white"][/TD]
[TD="class: xl89, width: 101, bgcolor: white"][/TD]
[TD="class: xl89, width: 104, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl89, width: 88, bgcolor: white"]9
[/TD]
[TD="class: xl88, width: 88, bgcolor: white"]A
[/TD]
[TD="class: xl90, width: 98, bgcolor: white"]03.02.2014
[/TD]
[TD="class: xl89, width: 98, bgcolor: white"][/TD]
[TD="class: xl89, width: 101, bgcolor: white"][/TD]
[TD="class: xl89, width: 104, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl89, width: 88, bgcolor: white"]10
[/TD]
[TD="class: xl88, width: 88, bgcolor: white"]A
[/TD]
[TD="class: xl90, width: 98, bgcolor: white"]06.01.2014
[/TD]
[TD="class: xl89, width: 98, bgcolor: white"][/TD]
[TD="class: xl89, width: 101, bgcolor: white"][/TD]
[TD="class: xl89, width: 104, bgcolor: white"][/TD]
[/TR]
</TBODY>[/TABLE]


Worksheet Formulas
Cell

<TBODY>
[TH="align: left"]Formula
[/TH]

[TH="width: 10, bgcolor: #dae7f5"]F2
[/TH]
[TD="align: left"]=SUMPRODUCT(--(ISNUMBER(SEARCH($D3;$B$2:$B$11)));--(TEXT($C$2:$C$11;"mmm")=TEXT(E$1;"mmm")))
[/TD]

</TBODY>


Please look at print screen. - F2=F3 on screen.
685t6u.png
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,222,170
Messages
6,164,382
Members
451,886
Latest member
elpepe1970

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