Summary

[Dhanesh Nair]

Hi There
I have the following data
All rows have items from a profit and loss account.
The Columns have dates for the month say 01-30.
Under each date there are 5 companies...which have data for all the P&L items
Is there a formula based way to summarise by month the totals for each company for each line item.

Hope I have clarified the issue

Thanks

D

 

[Khalid NGO]

Hello Dhanesh and welcome to the forum

Please upload a sample of your file and mention your expected result, it will ease members to provided targeted solutions.

Regards,

 

[Dhanesh Nair]

Hi Khalid

Apologies.

This is a sample file.

The second tab needs a quick formula based summary as this spreadsheet could go into a few hundred rows and a few columns ( extending for 12 months at least). I am after aan easy way to summarise month wise data for each item of expense and income for each company

Thanks Khalid

 

[Khalid NGO]

Hi Khalid

Apologies.

This is a sample file.

The second tab needs a quick formula based summary as this spreadsheet could go into a few hundred rows and a few columns ( extending for 12 months at least). I am after aan easy way to summarise month wise data for each item of expense and income for each company

Thanks Khalid

Hi Dhanesh,
Problem is there are merged cells in your sheets, I have used a helper row (yellow) in both sheets, and apply a sumproduct.

See if this is okay.

Regards,

 

[Khalid NGO]

Hi Dhanesh,
Problem is there are merged cells in your sheets, I have used a helper row (yellow) in both sheets, and apply a sumproduct.

See if this is okay.

Regards,

Oops a typo in formula

Please use this one:

=SUMPRODUCT((Sheet1!$B$2:$KO$2=B$2)*(Sheet1!$A$3:$A$18=$A3)*(YEAR(Sheet1!$B$19:$KO$19)=YEAR(B$19))*(MONTH(Sheet1!$B$19:$KO$19)=MONTH(B$19))*(Sheet1!$B$3:$KO$18))

 

[Dhanesh Nair]

Hi Khalid,

Sorry borthering you once again... tried the formula...seems like it is not working.
I tried a rather crude way of checking ( transpose and filter..but gives a different answer. )

Thanks Khalid

 

[Khalid NGO]

Hi Khalid,

Sorry borthering you once again... tried the formula...seems like it is not working.
I tried a rather crude way of checking ( transpose and filter..but gives a different answer. )

Thanks Khalid

No problem Dhanesh,
Can you share the result file?

 

[Dhanesh Nair]

Hi Khalid

Please find attached the result file. i tried checking the result for fee income for a few companies and the results varied .

ANZ : Fee income $459 against 188
CBA: Fee income $445 against $169
and so on..
Thanks

Dhanesh

 

[Khalid NGO]

Hi Khalid

Please find attached the result file. i tried checking the result for fee income for a few companies and the results varied .

ANZ : Fee income $459 against 188
CBA: Fee income $445 against $169
and so on..
Thanks

Dhanesh

Dear Dhanesh,
I think you have not tried the revised formula I posted in post # 5.

=SUMPRODUCT((Sheet1!$B$2:$KO$2=B$2)*(Sheet1!$A$3:$A$18=$A3)*(YEAR(Sheet1!$B$19:$KO$19)=YEAR(B$19))*(MONTH(Sheet1!$B$19:$KO$19)=MONTH(B$19))*(Sheet1!$B$3:$KO$18))

I have cross checked manually
CBA (Sep-2015) = 445
ANZ (Sep-2015) = 459

There was a typo in 2nd and Last array of my first formula (highlighted in Red)

Regards,