sumif on multiple sheet via vba

glebret

New Member
Joined
Sep 16, 2015
Messages
13
Hi,

I have a whole bunch of sheets and they all structured the same way. The all contain: the name of my client in column a, the real amount of expenses in column B, the budget amount in column C and a line for the total. I also have a summary sheets containing the name of all my clients and I would like to add the total of each column on that sheet. I have tried to get the total for just one column but there is a problem with one line of code:

Range("A3").Offset(lSht, 1).Formula = "=(SUMIF('" & Worksheets(lSht).Name & "'!C[1], ""*Total*""," '"Worksheets(lSht).Name& "'!C[3])"

All my variables or declared and working fine since I use them elsewhere.

Can anyone help me?

Thank you
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,777
Hi

You are using the R1C1 notation in the formula, but you are using the .Formula property of the range object, which is for the A1 notation. Try with the property .FormulaR1C1
 

glebret

New Member
Joined
Sep 16, 2015
Messages
13
Hi,

I've tried but it does not work.
B3 is were I want my data on my summary sheet but my problem is that all the other sheets can contain more than one "total" line (that's why I use a sumif) and they don't have the same quantity of expenses, meaning that my total line won't be the same on each sheet.

Thank you.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,777
Hi

I did a quick test with your formula

With a workbook with several of the default worksheets (Sheet1, Sheet2,...) and the last one named Totals,

with the worksheet Totals active,

I ran this test code and got in A4:A6:

A4: =SUMIF(Sheet1!C:C; "*Total*";Sheet1!E:E)
A5: =SUMIF(Sheet2!C:C; "*Total*";Sheet2!E:E)
A6: =SUMIF(Sheet3!C:C; "*Total*";Sheet3!E:E)




Code:
Sub Test()
Dim lSht As Long

For lSht = 1 To 3
      Range("A3").Offset(lSht, 1).FormulaR1C1 = "=SUMIF('" &  Worksheets(lSht).Name & "'!C[1], ""*Total*"",'" &  Worksheets(lSht).Name & "'!C[3])"
Next lSht
End Sub

Does this help?
 

glebret

New Member
Joined
Sep 16, 2015
Messages
13
Hi PGC,

Thanks, That helped a lot, I gad an extra parenthesis at the beginning of the formula, a "&" symbol and an apostrophe missing and also extra quotations marks. works great now. Thx

Best Regards

Glebret
 

Watch MrExcel Video

Forum statistics

Threads
1,099,376
Messages
5,468,248
Members
406,575
Latest member
Joe00

This Week's Hot Topics

Top