This may or may not be possible...... But I would like to be able to sum up
the numbers from one column only if they are

For example

A B
Expense Code Budgeted Amt.
111 $100
112 $200
111 $300

I would like a formula that add up only the dollar amounts for items whose
"Expense Code" is 111.......

There are forty line items with various expense codes and I need to be able
to total everything as well as break it down by expense code for a separate
table. I can't designate certain cells to be included because they are
constantly changing or are different for each case (110 sheets represent 110
different entities) so the formula must have the criteria from one cell
(Expense Code) to filter outthe dollar amounts.

Is this even possible? Any and all help will be greatly appreciated


Steve

=SUMIF(A:A,111,B:B)

or, put the "111" in C1 and use

=SUMIF(A:A,C1,B:B)

Vaya con Dios,
Chuck, CABGx3


"Steve" wrote in message
...
This may or may not be possible...... But I would like to be able to sum
up
the numbers from one column only if they are

For example

A B
Expense Code Budgeted Amt.
111 $100
112 $200
111 $300

I would like a formula that add up only the dollar amounts for items whose
"Expense Code" is 111.......

There are forty line items with various expense codes and I need to be
able
to total everything as well as break it down by expense code for a
separate
table. I can't designate certain cells to be included because they are
constantly changing or are different for each case (110 sheets represent
110
different entities) so the formula must have the criteria from one cell
(Expense Code) to filter outthe dollar amounts.

Is this even possible? Any and all help will be greatly appreciated


Steve

What if I want to make the criteria a range... such as if I want to sum up
all the dollar amounts for a Expense codes from 111 -120 to include
111,112,113,114, etc.

I put in, for example, =sumif(B4:B40,4400:4800,C4:C40) and I got "0" even
though there was a number in that range.... Did I screw it up some how?

Thanks

"CLR" wrote:

=SUMIF(A:A,111,B:B)

or, put the "111" in C1 and use

=SUMIF(A:A,C1,B:B)

Vaya con Dios,
Chuck, CABGx3


"Steve" wrote in message
...
This may or may not be possible...... But I would like to be able to sum
up
the numbers from one column only if they are

For example

A B
Expense Code Budgeted Amt.
111 $100
112 $200
111 $300

I would like a formula that add up only the dollar amounts for items whose
"Expense Code" is 111.......

There are forty line items with various expense codes and I need to be
able
to total everything as well as break it down by expense code for a
separate
table. I can't designate certain cells to be included because they are
constantly changing or are different for each case (110 sheets represent
110
different entities) so the formula must have the criteria from one cell
(Expense Code) to filter outthe dollar amounts.

Is this even possible? Any and all help will be greatly appreciated


Steve

Try either of these:

=SUM(SUMIF(B4:B40,{111,112,113,114,115,116,117,118 ,119,120},C4:C40))

OR

=SUMPRODUCT((B4:B40=111)*(B4:B40<=120)*C4:C40)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Steve" wrote in message
...
What if I want to make the criteria a range... such as if I want to sum up
all the dollar amounts for a Expense codes from 111 -120 to include
111,112,113,114, etc.

I put in, for example, =sumif(B4:B40,4400:4800,C4:C40) and I got "0" even
though there was a number in that range.... Did I screw it up some how?

Thanks

"CLR" wrote:

=SUMIF(A:A,111,B:B)

or, put the "111" in C1 and use

=SUMIF(A:A,C1,B:B)

Vaya con Dios,
Chuck, CABGx3


"Steve" wrote in message
...
This may or may not be possible...... But I would like to be able to
sum
up
the numbers from one column only if they are

For example

A B
Expense Code Budgeted Amt.
111 $100
112 $200
111 $300

I would like a formula that add up only the dollar amounts for items
whose
"Expense Code" is 111.......

There are forty line items with various expense codes and I need to be
able
to total everything as well as break it down by expense code for a
separate
table. I can't designate certain cells to be included because they
are
constantly changing or are different for each case (110 sheets
represent
110
different entities) so the formula must have the criteria from one
cell
(Expense Code) to filter outthe dollar amounts.

Is this even possible? Any and all help will be greatly appreciated


Steve

I appreciate the suggestions but.... these response seem to multiply and I
need to add them....

I guess my basic question is. Is there any way to a have a RANGE in the
Criteria portion of the SUMIF formula..... the range is hundreds of numbers
and would be impractical to list every one of them separated by commas....

Any and all suggestions would be appreciated greatly

Steve

"Ragdyer" wrote:

Try either of these:

=SUM(SUMIF(B4:B40,{111,112,113,114,115,116,117,118 ,119,120},C4:C40))

OR

=SUMPRODUCT((B4:B40=111)*(B4:B40<=120)*C4:C40)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Steve" wrote in message
...
What if I want to make the criteria a range... such as if I want to sum up
all the dollar amounts for a Expense codes from 111 -120 to include
111,112,113,114, etc.

I put in, for example, =sumif(B4:B40,4400:4800,C4:C40) and I got "0" even
though there was a number in that range.... Did I screw it up some how?

Thanks

"CLR" wrote:

=SUMIF(A:A,111,B:B)

or, put the "111" in C1 and use

=SUMIF(A:A,C1,B:B)

Vaya con Dios,
Chuck, CABGx3


"Steve" wrote in message
...
This may or may not be possible...... But I would like to be able to
sum
up
the numbers from one column only if they are

For example

A B
Expense Code Budgeted Amt.
111 $100
112 $200
111 $300

I would like a formula that add up only the dollar amounts for items
whose
"Expense Code" is 111.......

There are forty line items with various expense codes and I need to be
able
to total everything as well as break it down by expense code for a
separate
table. I can't designate certain cells to be included because they
are
constantly changing or are different for each case (110 sheets
represent
110
different entities) so the formula must have the criteria from one
cell
(Expense Code) to filter outthe dollar amounts.

Is this even possible? Any and all help will be greatly appreciated


Steve

Neither of my suggestions will *multiply* any values.

Is there any particular reason why you chose not to at *least try* the
suggestions before rejecting them?

If you have "hundreds" of numbers, the SumProduct formula would probably be
the way to go.

I would suggest for the *second* time (first was in my first post), to *try*
the suggestions.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Steve" wrote in message
...
I appreciate the suggestions but.... these response seem to multiply and I
need to add them....

I guess my basic question is. Is there any way to a have a RANGE in the
Criteria portion of the SUMIF formula..... the range is hundreds of
numbers
and would be impractical to list every one of them separated by commas....

Any and all suggestions would be appreciated greatly

Steve

"Ragdyer" wrote:

Try either of these:

=SUM(SUMIF(B4:B40,{111,112,113,114,115,116,117,118 ,119,120},C4:C40))

OR

=SUMPRODUCT((B4:B40=111)*(B4:B40<=120)*C4:C40)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Steve" wrote in message
...
What if I want to make the criteria a range... such as if I want to sum
up
all the dollar amounts for a Expense codes from 111 -120 to include
111,112,113,114, etc.

I put in, for example, =sumif(B4:B40,4400:4800,C4:C40) and I got "0"
even
though there was a number in that range.... Did I screw it up some how?

Thanks

"CLR" wrote:

=SUMIF(A:A,111,B:B)

or, put the "111" in C1 and use

=SUMIF(A:A,C1,B:B)

Vaya con Dios,
Chuck, CABGx3


"Steve" wrote in message
...
This may or may not be possible...... But I would like to be able
to
sum
up
the numbers from one column only if they are

For example

A B
Expense Code Budgeted Amt.
111 $100
112 $200
111 $300

I would like a formula that add up only the dollar amounts for
items
whose
"Expense Code" is 111.......

There are forty line items with various expense codes and I need to
be
able
to total everything as well as break it down by expense code for a
separate
table. I can't designate certain cells to be included because they
are
constantly changing or are different for each case (110 sheets
represent
110
different entities) so the formula must have the criteria from one
cell
(Expense Code) to filter outthe dollar amounts.

Is this even possible? Any and all help will be greatly
appreciated


Steve