SUMIF multiple criteria
 

I would like to be able to use SUMIF to return the total spent on various
activities within a dept (without having to have a separate column stating
the dept number).

Activity 121 $400
Activity 144 $700
Activity 126 $45
Activity 141 $1457

I want to SUM activities 121 - 129 and 131 - 139 and so on.

Is it possible to insert a range of criteria within the SUMIF function?

SUMIF multiple criteria
 

No.
You will have to use a SUMPRODUCT formula instead

BTW, what do you do with activities 120, 130, 140, ... ?

HTH
--
AP

"HLS" a écrit dans le message de
...
I would like to be able to use SUMIF to return the total spent on various
activities within a dept (without having to have a separate column stating
the dept number).

Activity 121 $400
Activity 144 $700
Activity 126 $45
Activity 141 $1457

I want to SUM activities 121 - 129 and 131 - 139 and so on.

Is it possible to insert a range of criteria within the SUMIF function?

SUMIF multiple criteria
 

Try:

=SUMPRODUCT(--(A1:A4=121),--(A1:A4<=129),--(B1:B4))


<A1:A4 ... activity numbers
<B1:B4 ....Spend

HTH

"HLS" wrote:

I would like to be able to use SUMIF to return the total spent on various
activities within a dept (without having to have a separate column stating
the dept number).

Activity 121 $400
Activity 144 $700
Activity 126 $45
Activity 141 $1457

I want to SUM activities 121 - 129 and 131 - 139 and so on.

Is it possible to insert a range of criteria within the SUMIF function?

SUMIF multiple criteria
 

Hi

Try something like this:
=SUMPRODUCT(--(A2:A100="Activity 120"),--(A2:A100<="Activity
129"),--(B2:B100))

Hope this helps.
Andy.

"HLS" wrote in message
...
I would like to be able to use SUMIF to return the total spent on various
activities within a dept (without having to have a separate column stating
the dept number).

Activity 121 $400
Activity 144 $700
Activity 126 $45
Activity 141 $1457

I want to SUM activities 121 - 129 and 131 - 139 and so on.

Is it possible to insert a range of criteria within the SUMIF function?

SUMIF multiple criteria
 

Although all of the reponses that I've had have suggested the same course of
action I can't make it work.

I didn't mention in my original query that the formula is in one work sheet
and the data is in another so I amended your solution to:

=SUMPRODUCT(--('Cost Detail'!B13:B98=121),--('Cost
Detail'!B13:B98<=129),--('Cost Detail'!J13:J98)).

Could this result in a #VALUE! response?

If not is there anything else in the formatting of either of the workbooks
that might prevent the formula from working (like subtotalling etc).

Thanks for your help.



"Toppers" wrote:

Try:

=SUMPRODUCT(--(A1:A4=121),--(A1:A4<=129),--(B1:B4))


<A1:A4 ... activity numbers
<B1:B4 ....Spend

HTH

"HLS" wrote:

I would like to be able to use SUMIF to return the total spent on various
activities within a dept (without having to have a separate column stating
the dept number).

Activity 121 $400
Activity 144 $700
Activity 126 $45
Activity 141 $1457

I want to SUM activities 121 - 129 and 131 - 139 and so on.

Is it possible to insert a range of criteria within the SUMIF function?

SUMIF multiple criteria
 

Hi again

Are you sure that J13:J98 is made up of numbers?

Andy.

"HLS" wrote in message
...
Although all of the reponses that I've had have suggested the same course
of
action I can't make it work.

I didn't mention in my original query that the formula is in one work
sheet
and the data is in another so I amended your solution to:

=SUMPRODUCT(--('Cost Detail'!B13:B98=121),--('Cost
Detail'!B13:B98<=129),--('Cost Detail'!J13:J98)).

Could this result in a #VALUE! response?

If not is there anything else in the formatting of either of the workbooks
that might prevent the formula from working (like subtotalling etc).

Thanks for your help.



"Toppers" wrote:

Try:

=SUMPRODUCT(--(A1:A4=121),--(A1:A4<=129),--(B1:B4))


<A1:A4 ... activity numbers
<B1:B4 ....Spend

HTH

"HLS" wrote:

I would like to be able to use SUMIF to return the total spent on
various
activities within a dept (without having to have a separate column
stating
the dept number).

Activity 121 $400
Activity 144 $700
Activity 126 $45
Activity 141 $1457

I want to SUM activities 121 - 129 and 131 - 139 and so on.

Is it possible to insert a range of criteria within the SUMIF function?