USING THE 'WHERE' CLAUSE IN EXCEL 1

• Jul 1, 2005

• #2

xlbo

MIS

Mar 26, 2002

15,080

GB

There is no "WHERE" clause in Excel - well, not unless you are running SQL in code. For what you seem to need, have a look at the SUMIF() function

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

Upvote 0 Downvote

• Jul 1, 2005

• Thread starter
• #3

ptweb

Programmer

Jul 1, 2005

37

GB

Can you give an example of the SUMIF() fucntion please?

 

Upvote 0 Downvote

• Jul 1, 2005

• #4

xlbo

MIS

Mar 26, 2002

15,080

GB

have a look in the help files that is what they are there for - this is NOT a helpdesk.

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

Upvote 0 Downvote

• Jul 1, 2005

• Thread starter
• #5

ptweb

Programmer

Jul 1, 2005

37

GB

SUMIF(range,criteria,sum_range)... last parameter still requires the order of where or IF

 

Upvote 0 Downvote

• Jul 1, 2005

• #6

xlbo

MIS

Mar 26, 2002

15,080

GB

And the example in the help files??

Code:

  A                B
1 Property Value  Commission 
2 100,000         7,000 
3 200,000         14,000 
4 300,000         21,000 
5 400,000         28,000 


Formula Description (Result) 
=SUMIF(A2:A5,">160000",B2:B5) Sum of the commissions for property values over 160000 (63,000)

I fail to see how "WHERE" comes into this anywhere...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

Upvote 0 Downvote

• Jul 1, 2005

• Thread starter
• #7

ptweb

Programmer

Jul 1, 2005

37

GB

Well I have a mixture of ids, on each case I would search on a criteria

IE: Equal to 21 for one row
equal to 31 for another, the sheet is a mixture of rowids so only certain ids ie: == 21 for one row in another sheet would apply, hope this makes sense

Gry

 

Upvote 0 Downvote

• Jul 1, 2005

• #8

S

SkipVought

Programmer

Dec 4, 2001

47,492

US

Gry,

Rather than speaking in riddles, please state your requrements in a clear, concise manner.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

Upvote 0 Downvote

• Jul 4, 2005

1
• #9

KenWright

Technical User

Mar 22, 2003

3,688

GB

=SUMIF(A2:A100,21,B2:B100)

Sum of the values in B2:B100 WHERE the corresponding rowID in A2:A100 is 21 (Assuming 21 is numeric)

If the 21 is not numeric then it must be enclosed in quotes within the formula, eg

=SUMIF(A2:A100,"21",B2:B100)

If you have a list of IDs in your spreadsheet in say cells J1:J5, and you wish to use this formula against each of those IDs in the list, then you can substitute the 21 or "21" for a cell reference, so in say K1 simply put the formula

=SUMIF(A$2:A$100,J1,B$2:B$100)

and then copy down to K5. Note that the row part of the references have been made absolute with the use of the $ signs so that they don't change when you copy down.

If this doesn't help either then you need to give an example of your data, state what you want to get out of it, and give us the answer you would expect from the data you provide.

If you wish to sum on more than one criteria then investigate the SUMPRODUCT function in Help.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

Upvote 0 Downvote

• Jul 4, 2005

• Thread starter
• #10

ptweb

Programmer

Jul 1, 2005

37

GB

This is near to the point Ken thxs ,... what if it was in a sheet called 'sales'

 

Upvote 0 Downvote

• Jul 4, 2005

• #11

ca8msm

Programmer

May 9, 2002

11,327

GB

If you try it yourself in Excel, you'll see you just have to use SheetName!Range...


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

Upvote 0 Downvote

• Jul 4, 2005

• Thread starter
• #12

ptweb

Programmer

Jul 1, 2005

37

GB

I tried that I get ZERO.. i tried it in the same sheet and it works

 

Upvote 0 Downvote

• Jul 5, 2005

• #13

KenWright

Technical User

Mar 22, 2003

3,688

GB

Give us the EXACT formulas you used when you tried it in the same sheet and also when you tried it against a different sheet. Don't type them out, actually go into the cell and copy the formulas themselves and then paste into your note.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

Upvote 0 Downvote

• Jul 5, 2005

• Thread starter
• #14

ptweb

Programmer

Jul 1, 2005

37

GB

Thxs ken, just realized my error, last paramter had no sheet called... D'OH!! Thxs Ken

 

Upvote 0 Downvote

• Jul 5, 2005

• #15

KenWright

Technical User

Mar 22, 2003

3,688

GB

No problem.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

Upvote 0 Downvote