Count/Sum based on another column date range/value

Dear Experts,

Good Day, How are you doing all? Hope all are safe & healthy in this pandemic situation.

I am facing problem to make reports based on raw data. I did some experiment to do it but I failed. Here I have attached the sheet for your understating & and explain inside what required in my reporting. Hope I could get some help from you.

Thanks in advance.

Regards,
Iqbal

For counts and sums with criteria in other columns, have a look at COUNTIF(S) and SUMIF(S).

Let us know if you need any further help once you have tried these for yourself.

Thanks for your reply.

Already I able to solve one part with the help of COUNTIFS & SUMIFS, but rest parts I cant do. Pls have a look in my attached sheet. also I have make some notes in the sheet for your understanding regarding problems & outcome

Perhaps the following will be of some help. On the One Year All Marketing sheet:
1. Paste the following into cell E3 and copy down:

Formula:

Please Login or Register  to view this content.

2. Paste the following into cell F3 and copy down:

Formula:

Please Login or Register  to view this content.

While attempting to help on the December -2020 I discovered that there are merged cells in both columns D and I on the 2020 sheet which is making it difficult to proceed. Those cells should be unmerged and values placed in each resulting blank cells. Once that is done make a new post to this thread and I will attempt to provide further help.
Let us know if you have any questions.

Dear @JeteMC Bro,

Thanks for your reply with some solution. I put your formula in the sheet 'One Year All Marketing' as you guided, the result seems to be OK.

Update: After applying your formula, there is no result from august to December, but there should be some result for those month. because Those month also have some value in Column J in sheet '2020'. proof attached.
p1.PNG

Aug.PNG

I already updated data sheet '2020' by unmerging the cells & placed a value in blanked cell. Pls help me there.

For some reason when the range is 5:12000 the formulas on the January 2020 sheet display a value error, however when the range is 5:2140 the formulas yield numbers.
For column D: =COUNTIFS('2020'!D$5:D$2140,">="&L$1,'2020'!D$5:D$2140,"<="&EOMONTH(L$1,0),'2020'!I$5:I$2140,B3)
For column E: =SUMIFS('2020'!F$5:F$2140,'2020'!D$5:D$2140,">="&L$1,'2020'!D$5:D$2140,"<="&EOMONTH(L$1,0),'2020'!I$5:I$2140,B3)
For column F: =COUNTIFS('2020'!D$5:D$2140,">="&L$1,'2020'!D$5:D$2140,"<="&EOMONTH(L$1,0),'2020'!I$5:I$2140,B3,'2020'!J$5:J$2140,"<>")
For column G: =SUMIFS('2020'!F$5:F$2140,'2020'!D$5:D$2140,">="&L$1,'2020'!D$5:D$2140,"<="&EOMONTH(L$1,0),'2020'!I$5:I$2140,B3,'2020'!J$5:J$2140,"<>")
Let us know if you have any questions.

My suggestion would be to convert the range with data on the 2020 sheet into a table. As the data covers rows 5:2140 the formulas on the other two sheets should reference those rows. When the table is expanded, by selecting cell L2140 and pressing the tab key, the formulas should automatically adjust to include the added rows.
I believe that doing so has corrected the other issues.
Let us know if you have any questions.

Dear @JateMC Bro,

Thanks for your suggestion & the help you giving me till now. Yes I believe converting data sheet to Table range would be good solution for the issue.

But for some reason I need to keep/do some merged cell within column [grouped data] in Data sheet except the reference columns which are used in another sheets [formulas] for making reports. Like, column A in sheet '2020'.

So, in that case how could I merge those, as merge not working in Table formats?

If I understand correctly that you can not convert the range A4:L2140 on the 2020 sheet into a table then you might try the following:
1. Select A2141:L2141
2. Press the Ctrl, Shift and down arrow keys
3. Select Clear All from the Editing screen on the Home tab
You should now be able to rewrite the formulas to reference rows 5:12000 as you were doing originally.
Let us know if you have any questions.

Try:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.