SUMIFS function help
 

Hello All,

I am sort of making an expense sheet. I have multiple categories and
subcategories in it. I make expense entry in the sheet and then there is a
separate column where I want expenses as per major categories. For example, I
have utilities as a major category and utilities-internet,
Utilities-electricity etc. as subcategories. When I enter expenses I use
subcategories.

For getting sum of expenses falling under one major category, I am using
SUMIFS function. The issue that is baffling me is, I can not use multiple
subcategories. If I use only one subcategory, I get the details but if I try
to add more than one, it doesn't work.
My current formula example is =SUMIFS(E5:E50, C5:C50, "=Utilities-Internet,
Utilities-Phones, Utilities-electricity"), amounts are in column E and
categories are in column C.
I tried removing space after comma, tried putting slash (/) instead of
comma, it didn't work.

Can anyone tell me whats wrong with my formula?

Thanks

SUMIFS function help
 

Hi,

I can't do sumifs because I don't have Excel 2007 but here's a sumproduct
version

=SUMPRODUCT((LEFT(C5:C50,9)="Utilities")*(E5:E50))

I'm not to sure of the syntax for sumifs either so my alternative may be the
wrong way around. If it is try this

=SUMPRODUCT((LEFT(E5:E50,9)="Utilities")*(C5:C50))

Mike

"teejay" wrote:

Hello All,

I am sort of making an expense sheet. I have multiple categories and
subcategories in it. I make expense entry in the sheet and then there is a
separate column where I want expenses as per major categories. For example, I
have utilities as a major category and utilities-internet,
Utilities-electricity etc. as subcategories. When I enter expenses I use
subcategories.

For getting sum of expenses falling under one major category, I am using
SUMIFS function. The issue that is baffling me is, I can not use multiple
subcategories. If I use only one subcategory, I get the details but if I try
to add more than one, it doesn't work.
My current formula example is =SUMIFS(E5:E50, C5:C50, "=Utilities-Internet,
Utilities-Phones, Utilities-electricity"), amounts are in column E and
categories are in column C.
I tried removing space after comma, tried putting slash (/) instead of
comma, it didn't work.

Can anyone tell me whats wrong with my formula?

Thanks

SUMIFS function help
 

=SUM(SUMIF(C5:C50,"Utilities-"&{"Internet","Phones","electricity"},E5:E50))



"teejay" wrote:

Hello All,

I am sort of making an expense sheet. I have multiple categories and
subcategories in it. I make expense entry in the sheet and then there is a
separate column where I want expenses as per major categories. For example, I
have utilities as a major category and utilities-internet,
Utilities-electricity etc. as subcategories. When I enter expenses I use
subcategories.

For getting sum of expenses falling under one major category, I am using
SUMIFS function. The issue that is baffling me is, I can not use multiple
subcategories. If I use only one subcategory, I get the details but if I try
to add more than one, it doesn't work.
My current formula example is =SUMIFS(E5:E50, C5:C50, "=Utilities-Internet,
Utilities-Phones, Utilities-electricity"), amounts are in column E and
categories are in column C.
I tried removing space after comma, tried putting slash (/) instead of
comma, it didn't work.

Can anyone tell me whats wrong with my formula?

Thanks

SUMIFS function help
 

Without opening 2007 and testing try
=SUMIFS(E5:E50, C5:C50, =({"Utilities-Internet",
"Utilities-Phones","Utilities-electricity"}),

this works in 2003
=SUMPRODUCT((TRIM(ChecksC)={"e","w","s","g"})*(Che cksD))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"teejay" wrote in message
...
Hello All,

I am sort of making an expense sheet. I have multiple categories and
subcategories in it. I make expense entry in the sheet and then there is a
separate column where I want expenses as per major categories. For
example, I
have utilities as a major category and utilities-internet,
Utilities-electricity etc. as subcategories. When I enter expenses I use
subcategories.

For getting sum of expenses falling under one major category, I am using
SUMIFS function. The issue that is baffling me is, I can not use multiple
subcategories. If I use only one subcategory, I get the details but if I
try
to add more than one, it doesn't work.
My current formula example is =SUMIFS(E5:E50, C5:C50,
"=Utilities-Internet,
Utilities-Phones, Utilities-electricity"), amounts are in column E and
categories are in column C.
I tried removing space after comma, tried putting slash (/) instead of
comma, it didn't work.

Can anyone tell me whats wrong with my formula?

Thanks

SUMIFS function help
 

Try this:

=SUMIF(C5:C50,"Utilities-*",E5:E50)

--
Biff
Microsoft Excel MVP


"teejay" wrote in message
...
Hello All,

I am sort of making an expense sheet. I have multiple categories and
subcategories in it. I make expense entry in the sheet and then there is a
separate column where I want expenses as per major categories. For
example, I
have utilities as a major category and utilities-internet,
Utilities-electricity etc. as subcategories. When I enter expenses I use
subcategories.

For getting sum of expenses falling under one major category, I am using
SUMIFS function. The issue that is baffling me is, I can not use multiple
subcategories. If I use only one subcategory, I get the details but if I
try
to add more than one, it doesn't work.
My current formula example is =SUMIFS(E5:E50, C5:C50,
"=Utilities-Internet,
Utilities-Phones, Utilities-electricity"), amounts are in column E and
categories are in column C.
I tried removing space after comma, tried putting slash (/) instead of
comma, it didn't work.

Can anyone tell me whats wrong with my formula?

Thanks

SUMIFS function help
 

Thank you everyone for taking time to answer my query. I tried T. Valko's
option (as it was the smallest and understandable formula to me) and it
worked.

"T. Valko" wrote:

Try this:

=SUMIF(C5:C50,"Utilities-*",E5:E50)

--
Biff
Microsoft Excel MVP

SUMIFS function help
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"teejay" wrote in message
...
Thank you everyone for taking time to answer my query. I tried T. Valko's
option (as it was the smallest and understandable formula to me) and it
worked.

"T. Valko" wrote:

Try this:

=SUMIF(C5:C50,"Utilities-*",E5:E50)

--
Biff
Microsoft Excel MVP