Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Why can't I use the MONTH function within the SUMIFS statement?

I have a Detail worksheet with three named ranges: Category, DateOfService
and Amount.

I have a Summary worksheet where I am trying to summaries the amounts by
Category (represented by the rows) and Month (represented by the columns).

Why do I get an error when I try to use this formula on the Summary sheet?

=SUMIFS(Amount,Category,A2,Month(DateOfService),1)

where A2 is the first row in the summary data.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Why can't I use the MONTH function within the SUMIFS statement?

The reason you are getting an error when using the MONTH function within the SUMIFS statement is because the MONTH function is not a valid argument for the SUMIFS function. The SUMIFS function only allows for range/criteria pairs to be specified.

To achieve the desired result of summarizing the amounts by Category and Month, you can add a helper column to your Detail worksheet that extracts the month from the DateOfService column using the MONTH function. Then, you can use this helper column as a criteria in your SUMIFS function on the Summary worksheet.

Here are the steps to add a helper column:
  1. Insert a new column next to the DateOfService column.
  2. In the first cell of the new column, enter the formula =MONTH(DateOfService).
  3. Copy the formula down to all the cells in the column.

Now that you have a helper column, you can use the following formula in your Summary worksheet:

=SUMIFS(Amount,Category,A2,HelperColumn,1)

where HelperColumn is the column you just created that contains the month extracted from the DateOfService column.

This formula will sum the amounts in the Amount column where the Category matches the value in cell A2 and the month in the HelperColumn is 1 (January). You can copy this formula to the other cells in the summary table to summarize the amounts by Category and Month.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 834
Default Why can't I use the MONTH function within the SUMIFS statement?

You just can't, it's daft isn't it, makes SUMIFS more or less pointless IMO.

Try this

=SUMPRODUCT(--(Category=A2),--(Month(DateOfService)=1),Amount)

--

HTH

Bob

"djhunt77" wrote in message
...
I have a Detail worksheet with three named ranges: Category, DateOfService
and Amount.

I have a Summary worksheet where I am trying to summaries the amounts by
Category (represented by the rows) and Month (represented by the columns).

Why do I get an error when I try to use this formula on the Summary sheet?

=SUMIFS(Amount,Category,A2,Month(DateOfService),1)

where A2 is the first row in the summary data.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 47
Default Why can't I use the MONTH function within the SUMIFS statement?

On Mar 28, 7:28*am, djhunt77
wrote:
I have a Detail worksheet with three named ranges: Category, DateOfService
and Amount.

I have a Summary worksheet where I am trying to summaries the amounts by
Category (represented by the rows) and Month (represented by the columns)..

Why do I get an error when I try to use this formula on the Summary sheet?

=SUMIFS(Amount,Category,A2,Month(DateOfService),1)

where A2 is the first row in the summary data.



Any chance that with "Category" and "A2" you are mixing text and
values? Or is your "DateOf Service" text?

Break it into single criteria and see where the problem is.

I use a formla just like that and it works fine.

Else Bob's SUMPRODUCT formula with the unary works fine.
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Why can't I use the MONTH function within the SUMIFS statement?

Why do I get an error when I try to use this formula
=SUMIFS(Amount,Category,A2,Month(DateOfService),1 )


SUMIF
SUMIFS
COUNTIF
COUNTIFS
AVERAGEIF
AVERAGEIFS

These functions can only handle "straight" comparisons. That is, you can't
manipulate a range array to test for a condition.

In the formula above you're trying to manipulate the range array
DateOfService by first testing for the month.

MONTH(DateOfService) = 1

The test has to be a "straight" comparison:

DateOfService = 1

Of course, that doesn't do what you want so you need to use a different
function as Bob suggested.

--
Biff
Microsoft Excel MVP


"djhunt77" wrote in message
...
I have a Detail worksheet with three named ranges: Category, DateOfService
and Amount.

I have a Summary worksheet where I am trying to summaries the amounts by
Category (represented by the rows) and Month (represented by the columns).

Why do I get an error when I try to use this formula on the Summary sheet?

=SUMIFS(Amount,Category,A2,Month(DateOfService),1)

where A2 is the first row in the summary data.





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 47
Default Why can't I use the MONTH function within the SUMIFS statement?


Yu can use that convention if you're willing to use an array formula

e.g. this works. You'd have to change names etc.

~=SUM((DOSDEP=$B19)*(DOS_Acct =$C19)*(DOS_Acct <=
$D19)*(MONTH(DOS)=MONTH($G$3))*(DOSAmount))

Here I'm looking up both the month of the range month and the column
header.

Siegfried

Ctrl-Shift-Enter
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Why can't I use the MONTH function within the SUMIFS statement

Thanks Bob - that suggestion worked perfectly!

"Bob Phillips" wrote:

You just can't, it's daft isn't it, makes SUMIFS more or less pointless IMO.

Try this

=SUMPRODUCT(--(Category=A2),--(Month(DateOfService)=1),Amount)

--

HTH

Bob

"djhunt77" wrote in message
...
I have a Detail worksheet with three named ranges: Category, DateOfService
and Amount.

I have a Summary worksheet where I am trying to summaries the amounts by
Category (represented by the rows) and Month (represented by the columns).

Why do I get an error when I try to use this formula on the Summary sheet?

=SUMIFS(Amount,Category,A2,Month(DateOfService),1)

where A2 is the first row in the summary data.



.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumifs statemenet returns - , how to use in if statement? Robbro Excel Discussion (Misc queries) 2 February 17th 10 01:30 PM
SUMIFS-time criteria/month Petran Excel Worksheet Functions 5 November 6th 09 12:34 PM
SUMIFs function lefty Excel Worksheet Functions 2 October 24th 09 10:29 PM
using sumifs to sum based on month, and criteria Jonas Excel Worksheet Functions 13 April 29th 08 01:12 PM
Can SUMIFS use the OR function? Ted M H Excel Worksheet Functions 7 January 2nd 08 11:18 PM


All times are GMT +1. The time now is 02:29 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"