=SUMIF() question : can the criteria be a range of things?

[Richard1982]

Hello,

Apologies is this is a silly question... but can the criteria in a SUMIF() be a range of values? For example I have:

=SUMIF(A1:A100,7000,B1:B100)

I have a list of product codes in column A and values in column B - I'm looking for product code 7000. All the 7000's are added up and so I know the total, that bit is good.

But now I want a total of everything between 7000 and 7413. Is there someway to make that? Something like this (although obviously this doesn't work):

=SUMIF(A1:A100,7000:7413,B1:B100)

At the moment I have the 7413 lines and just sum them up but I'm sure there must be a way to just have a one line formular that add it all up! Any help would be greatfully received!

Richard

 

[arthurbr]

Perhaps

=sumifs(B1:B100,A1:A100,">7000",A1:A100,"<7413")

 

[Richard1982]

Ah thank you Arthur - simple when you know how! I'm kicking myself for not thinking of this.

Thanks for your help!

 

[Richard1982]

 

[myall_blues]

Try

=SUMPRODUCT(0+(A1:A1000>"7000"),0+(A1:A1000<"7413"),B1:B1000)

Regards

Murray

 

[MrRajKumar]

Or,

enter required product code in a range, eg: C2:C50, then use

=SUM(SUMIFS(B1:B100,A1:A100,C2:C50))

 

[arthurbr]

Try

=SUMPRODUCT(0+(A1:A1000>"7000"),0+(A1:A1000<"7413"),B1:B1000)

Regards

Murray

In this case "7000" is text, not a number

 

[arthurbr]

Ah thank you Arthur - simple when you know how! I'm kicking myself for not thinking of this.

Thanks for your help!

You're welcome

 

[myall_blues]

In this case "7000" is text, not a number

@arthurbr OP posted a subsequent question, now gone and replaced by a smiley face, looking for a solution because all the values in column A were preceded by a quote (‘7000).

 

[arthurbr]

@arthurbr OP posted a subsequent question, now gone and replaced by a smiley face, looking for a solution because all the values in column A were preceded by a quote (‘7000).

Thanks. The OP makes things confusing for others that way