VBA for (SUMIFS + SUMIFS)

WI_123

New Member
Joined
Mar 11, 2019
Messages
3
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.

Dec 1, 2018A123SUMIFS(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, 2018B123SUMIFS(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, 2018C123SUMIFS(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.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

jjasmith4

New Member
Joined
Aug 22, 2018
Messages
48
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

New Member
Joined
Mar 11, 2019
Messages
3
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:

DateClientSales
1/12/2018A12330
1/12/2018B12370
1/12/2018C12325
2/12/2018A123-50
2/12/2018B123100
2/12/2018C12320
3/12/2018A12380
3/12/2018B12330
3/12/2018C12360

<colgroup><col width="87" style="width: 87pt;"><col width="65" span="2" style="width: 65pt;"></colgroup><tbody>
</tbody>

Output sheet I am trying to get: the "125" value is the total of client A,B & C on Dec 1, 2018.
ClientDateSales
A1231/12/2018125
B1232/12/201870
C1233/12/2018170

<colgroup><col width="65" span="3" style="width: 65pt;"></colgroup><tbody>
</tbody>


Thanks.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,804
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 11, 2019
Messages
3
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

New Member
Joined
Aug 22, 2018
Messages
48
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,293
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top