compare dates and sum unique values

[meus]

Hey all!

I have two tables in a worksheet. There is a date in both the table. What i want to do is that compare the date between two tables and if the date in table 1 is unique to table 2, take that value and add all the unique..

I have attached the sheet, where it will be more clear to understand..

Any help will be appreciated.

Thanks.

[hemesh]

assuming your table lengths are same then
in k9 copy paste below
=SUMPRODUCT(ISNA(MATCH(Table1[Date],Table2[Date],0))*Table1[Amount])
in K10
=SUMPRODUCT(ISNA(MATCH(Table2[Date],Table1[Date],0))*Table2[Amount])

[meus]

.

Thanks.. It works Great

[hemesh]

you are welcome Meus and thanks for rep

[meus]

Hey thanks

It works great as above but can u help little more on same case please?
Now, i have another more column in each table "Particulars"..

Before we just compared dates from two tables and SUM the unique date values... Now i want to compare the date as well as the particulars.. If the date is same but the Particular is not same, it should also be added..

Can this be done??

Please find the attached...

Thanks a lot

[samba_ravi]

Please Login or Register  to view this content.

Try the above formula

[meus]

Nop.. it is not correct...

Thank you anyway..

[shukla.ankur281190]

You just need to add a more argument in post# 3 formula.

M9=SUMPRODUCT(ISNA(MATCH(Table1[Date],Table2[Date],0))*(Table1[Particulars]<>Table2[Particulars])*Table1[Amount])
M10=SUMPRODUCT(ISNA(MATCH(Table2[Date],Table1[Date],0))*(Table2[Particulars]<>Table1[Particulars])*Table2[Amount])

[meus]

Hi Sukla Ankur,

The formula is behaving as same as post number 2.
It is just summarizing the unique dates, now it should consider both dates and particulars to be unique..

For table1 result should be 192000 i.e M9 Value

Hope this makes sense..

Thanks

[shukla.ankur281190]

Try

M9=SUMPRODUCT(ISNA(MATCH(Table1[Particulars]&Table1[Date],INDEX(Table2[Particulars]&Table2[Date],),0))*Table1[Amount])

[meus]

Thank you.. thats what i wanted... Thanks a lot..

[shukla.ankur281190]

Glad to help you and thanks for feedback