Total weekly wages by month

[Guest]

I have tried using SUMIF but am stuck in 'criteria'. I have two columns with
week date and amount for each employee. I want to total and show the totals
for each month.

 

[Bernie Deitrick]

Bill,

Prefered method:

Use a Pivot table, with the dates as the row fields, and then group by date. No formulas involved.

Or, use a formula like

=SUMPRODUCT((MONTH(C3:C25)=2)*D325)

where column C has the dates, and D the numbers, and you want February results.
(This could be modified to make a table by using

=SUMPRODUCT((MONTH($C$3:$C$25)=G2)*$D$3:$D$25)

Where G2 has a month number, and the numbers continue down column G...

Another method.

Use a helper column with the formula

=MONTH(cell with date)

and base the SUMIF on that column.



HTH,
Bernie
MS Excel MVP

 

[Guest]

Have tried 'SUMPRODUCT' formula and what I get in the cell is '#VALUE'. I
must be doing something wrong. The sheet is in employee order, one employee
under the last and totalled. I cannot change the order by sorting for a Pivot
table.

 

[Bernie Deitrick]

Bill,

Are the dates actual dates? or strings that look a little like dates? Change the formatting of the
cell to number, and the date should change to a number....

Otherwise, you have strings, and need to take other steps to change to actual dates.

HTH,
Bernie
MS Excel MVP

 

[Guest]

I enter the dates as 13/10/06 and it shows in the cell as 13-Oct-06. I want
to retain this format for ease of reading.

 

[Bernie Deitrick]

Bill,

Then the dates are OK. The ranges referenced by the MONTH function part of the SUMPRODUCT can only
have dates - no headers, text, etc. or that will throw up an error. In my example formula,

=SUMPRODUCT((MONTH($C$3:$C$25)=G2)*$D$3:$D$25)

C2 had a header "Dates", and C3 to C25 had actual dates. G2 has the number 2 (for February), and
D325 have numbers that I want to sum. D2 had the header "Values".....

HTH,
Bernie
MS Excel MVP

 

[Guest]

I have details of 15 employees with totals of wages for each. I have tried
SUMPRODUCT((MONTH(B13:B17,B197:B228)=2)*(C13:C17,C197:C228)) to no avail.
Also tried
SUMIF(B13:B17,B197:B228),MONTH(B13:B17,B197:B228),(C13:C17,C197:c228))
without success. Tried + and * and ; instead of , as seperator - no good.
What can I do please?

 

[Bernie Deitrick]

Bill,

=SUMPRODUCT((MONTH(B13:B17)=2)*(C13:C17)) + SUMPRODUCT((MONTH(B197:B228)=2)*(C197:C228))

You have to have contiguous ranges....

HTH,
Bernie
MS Excel MVP

 

[Guest]

Many thanks Bernie. I don't believe this is a good solution because I have up
to 20 ranges to cover which would give 820 characters in the instruction line
(I showed 2 ranges as an example). Maybe a Pivot Table in the same sheet
lower down would work but each week many ranges will be increased by one row
(the wages for that week) and the Pivot Table would have to be adjusted
automatically or with a macro. This seems to be getting cumbersome. Many
thanks anyway for your assistance and forbearance.
Bill

 

[Bernie Deitrick]

Bill,

It's easy to automatically update the range for a pivot table.

Let's say that your current data is in rows 1 to 100. Set your Pivot table to reference rows 1 to
101, and then wehen you need to add data, insert enough rows between rows 100 and 101 (by selecting
row 101 and doing the insert) to hold the new data. Row 101 will be moved down (and should remain
blank) and Excel will automatically expand the PT reference range to suit. It's a good idea to
format the last row with a special color, and then you'll remember that you need to insert rows
BEFORE that row to expand the PT range.

HTH,
Bernie
MS Excel MVP