Hi all,
I have a situation where I need to do SUMIFS + SUMIFS + SUMIFS to get the desired result. For example, in the table below I am trying to populate the third column - essentially trying to add the data for client A123 + client B123 + client C123 for Dec 1,2018 in cell c1. Similarly, trying to do the same (adding data for client A123 + client B123 + client C123) for Dec 2,2018 in cell c2.
<tbody>
</tbody>
The SUMIFS + SUMIFS formula works however, I have a lot of data and the number of clients are well over 500 and it is for 1 year so I have 365 dates. So the formula becomes too big for excel.
How am I able to do this efficiently using VBA? My VBA skills are not the best and after some trial and error, I am stuck.
Any help would be greatly appreciated.
Thanks.
I have a situation where I need to do SUMIFS + SUMIFS + SUMIFS to get the desired result. For example, in the table below I am trying to populate the third column - essentially trying to add the data for client A123 + client B123 + client C123 for Dec 1,2018 in cell c1. Similarly, trying to do the same (adding data for client A123 + client B123 + client C123) for Dec 2,2018 in cell c2.
Dec 1, 2018 | A123 | SUMIFS(DataA1:A9,DataB2:B9,"Dec 1,2018",DataC2:C9,"A123")+SUMIFS(DataA1:A9,DataB2:B9,"Dec 1,2018",DataC2:C9,"B123")+SUMIFS(DataA1:A9,DataB2:B9,"Dec 1,2018",DataC2:C9,"C123") |
Dec 2, 2018 | B123 | SUMIFS(DataA1:A9,DataB2:B9,"Dec 2,2018",DataC2:C9,"A123")+SUMIFS(DataA1:A9,DataB2:B9,"Dec 1,2018",DataC2:C9,"B123")+SUMIFS(DataA1:A9,DataB2:B9,"Dec 1,2018",DataC2:C9,"C123") |
Dec 3, 2018 | C123 | SUMIFS(DataA1:A9,DataB2:B9,"Dec 3,2018",DataC2:C9,"A123")+SUMIFS(DataA1:A9,DataB2:B9,"Dec 1,2018",DataC2:C9,"B123")+SUMIFS(DataA1:A9,DataB2:B9,"Dec 1,2018",DataC2:C9,"C123") |
<tbody>
</tbody>
The SUMIFS + SUMIFS formula works however, I have a lot of data and the number of clients are well over 500 and it is for 1 year so I have 365 dates. So the formula becomes too big for excel.
How am I able to do this efficiently using VBA? My VBA skills are not the best and after some trial and error, I am stuck.
Any help would be greatly appreciated.
Thanks.