VBA for (SUMIFS + SUMIFS)

[WI_123]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Dec 1, 2018[/TD]
[TD]A123[/TD]
[TD]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")[/TD]
[/TR]
[TR]
[TD]Dec 2, 2018[/TD]
[TD]B123[/TD]
[TD]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")[/TD]
[/TR]
[TR]
[TD]Dec 3, 2018[/TD]
[TD]C123[/TD]
[TD]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")[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[jjasmith4]

Please post a sample, even fictitious, of the Data sheet. I think converting the data to a table and adding table formulas might do the trick better, certainly faster, than VBA

 

[WI_123]

Please post a sample, even fictitious, of the Data sheet. I think converting the data to a table and adding table formulas might do the trick better, certainly faster, than VBA

Please, find below the sample sheets. I am trying to automate the formulas in column C (Sales highlighted in yellow). Once the range of column A gets too big the formula doesn't work and takes a very long time to calculate, hence I was wondering if there is a better/more efficient way of doing this using VBA.

Data sheet:

[TABLE="width: 217"]
<colgroup><col width="87" style="width: 87pt;"><col width="65" span="2" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="width: 87"]Date[/TD]
[TD="width: 65"]Client[/TD]
[TD="width: 65"]Sales[/TD]
[/TR]
[TR]
[TD]1/12/2018[/TD]
[TD]A123[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]1/12/2018[/TD]
[TD]B123[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]1/12/2018[/TD]
[TD]C123[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]2/12/2018[/TD]
[TD]A123[/TD]
[TD="align: right"]-50[/TD]
[/TR]
[TR]
[TD]2/12/2018[/TD]
[TD]B123[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]2/12/2018[/TD]
[TD]C123[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]3/12/2018[/TD]
[TD]A123[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]3/12/2018[/TD]
[TD]B123[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]3/12/2018[/TD]
[TD]C123[/TD]
[TD="align: right"]60[/TD]
[/TR]
</tbody>[/TABLE]

Output sheet I am trying to get: the "125" value is the total of client A,B & C on Dec 1, 2018.
[TABLE="width: 195"]
<colgroup><col width="65" span="3" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 65"]Client[/TD]
[TD="class: xl63, width: 65"]Date[/TD]
[TD="class: xl63, width: 65"]Sales[/TD]
[/TR]
[TR]
[TD="class: xl63"]A123[/TD]
[TD="class: xl63"]1/12/2018[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]125[/TD]
[/TR]
[TR]
[TD="class: xl63"]B123[/TD]
[TD="class: xl63"]2/12/2018[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]70[/TD]
[/TR]
[TR]
[TD="class: xl63"]C123[/TD]
[TD="class: xl63"]3/12/2018[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]170[/TD]
[/TR]
</tbody>[/TABLE]


Thanks.

 

[Norie]

If all you want to do is get the total sales for each date for all clients you don't need to include the client in the SUMIFS, in fact you could do it with SUMIF.

Even better you could use a pivot table to get the results.

 

[WI_123]

If all you want to do is get the total sales for each date for all clients you don't need to include the client in the SUMIFS, in fact you could do it with SUMIF.

Even better you could use a pivot table to get the results.

The problem is the actual data set is around 200,000 rows and I am trying to be able to enter just the desired clients (such as just client A&B) and sum their data for all 365 days.. in other instances I could be looking for just client B&C and sum their data for all 365 days..I am trying to build like a calculator of sorts if that makes sense.
Thanks.

 

[jjasmith4]

If you construct a pivot table with client and date as your row headings and sum of sales as your value, the only reason you'd need VBA is to refresh the table every time you make a change in it. In fact the pivot table would correctly show any brand new client you add -- that's a good thing, right?

I wouldn't even bother with the formulas you originally posted or any VBA coding at all. Just make the pivot table, and remember to refresh it every now and then. You can even have it generate all subtotals, then drag the date header to the left or right of the client header to see subtotals by day or client.