If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 news 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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 news 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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 news 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. . |
Thread Tools | |
Display Modes | |
|
|