Sumifs value return max date across two sheets

[silambarasan.J]

Hello Experts,

Herewith attached three sheets are Data1,Data2 and Required format.

1.I have try to sumifs value against of max date across with two sheets.

I have apply the following formula in column B4=SUMIFS(DATA!$D:$D,DATA!$A:$A,'REQUIRED FORMAT3'!$A4,DATA!$C:$C,MAX(DATA!$C:$C))/100000 and

C4=SUMIFS(DATA!$D:$D,DATA!$A:$A,'REQUIRED FORMAT3'!$A4,DATA!$E:$E,MAX(DATA!$E:$E))/100000 both of formulas working fine to refer single sheets.

But to refer two sheets value return wrongly.

2.Count of Dispatch against multiple line item across with two sheets.

DATE *********COUNT OF DISPATCH

02/01/2016********** 6
04/01/2016********** 18
05/01/2016********** 8
06/01/2016********** 18
07/01/2016********** 18
08/01/2016********** 13
09/01/2016********** 2

3.End of Day to return starting Min Number and max serial number across with two sheets.

MIN NO END OF DAY 11897
MAX NO END OF DAY 11898

please help me.

[José Augusto]

Try these formula to B4

Please Login or Register  to view this content.

[hemesh]

@ sachin can you be more specific that what you are trying to achieve

[silambarasan.J]

Thanks for your reply.

[silambarasan.J]

Thanks for your reply.

Formula return is perfect when the both sheets max dates range are same. At the same time if the both sheets max dates range are differ the value return is wrong as only considering the first max value.

Please Login or Register  to view this content.

My required format should be considering the both sheets max dates to against the sum of value corresponding to the branch.

Please also look at my other quary 2 & 3, I also tried searching online for the results but had no luck.

[hemesh]

@ JS_Sachin ! Hope you will take sometime to let s know what are the criteria how you are expecting your results and on what parameter

[José Augusto]

In C4 try these

Please Login or Register  to view this content.

[silambarasan.J]

Please give me some time to check because I don't have a system to check.this formulas applicable B4.Thanks for your reply.

[hemesh]

@Js_sachin hope I hope you will have time to update what you are looking for and how you want the count to occur!

because you have shown count of 6 for date 2 but i think it is much higher then 6.

[José Augusto]

@Js_sachin hope I hope you will have time to update what you are looking for and how you want the count to occur!

because you have shown count of 6 for date 2 but i think it is much higher then 6.

Hi hemesh.
I think that count as for unique values of column CUST

[hemesh]

@ jose you are right ! good eye,

[silambarasan.J]

Thank you so much for your reply Mr.José Augusto and hemesh.

Querry 2 and 3.

From Data1 and Data 2 both of sheets i have updated series no in column F against of column E(dispatch date).

if column F blanks do not consider count of dispatch.

i need how many records passed on single day against of date and month.

Data1:

CUST BILL NO*BILL DATE********VALUE***********DISPATCH*******SERIES NO

CHEN****5707****02/01/2016******187927.03*******02/01/2016******11814
CHEN****5905****02/01/2016******158780.34*******02/01/2016******11814

DELH****5757****02/01/2016******76704 *******02/01/2016******11815

FARI****5788****02/01/2016******64918.81 *******02/01/2016******11809

GURG****5874****02/01/2016******75079.37 *******02/01/2016******11812

HYDE****5967****02/01/2016******139837.11*******02/01/2016******11813

MUMB****5749****02/01/2016******152733.48*******02/01/2016******11811

POND 5903 02/01/2016 53584.61 02/01/2016******

Data2

CUST BILL NO *BILL DATE VALUE**** DISPATCH DATE SERIES NO

AHME****9235****06/01/2016******23023.76******* 09/01/2016***** 11897
AHME****0726****07/01/2016******131243.82****** 09/01/2016***** 11897
AHME****2114****08/01/2016******134889.31****** 09/01/2016***** 11897
AHME****2161****08/01/2016******35232.49******* 09/01/2016***** 11897

SURA****9236****06/01/2016******36352.62******* 09/01/2016***** 11898
SURA****0729****07/01/2016******67533.78******* 09/01/2016***** 11898

REQUIRED FORMAT

DATE *********COUNT OF DISPATCH

02/01/2016********** 6

09/01/2016********** 2

Herewith attachment please help me.