USING THE 'WHERE' CLAUSE IN EXCEL 1

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

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

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

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

And the example in the help files??

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

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

Gry,

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

Skip,
[sub]
[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! [/sub]

=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
----------------------------------------------------------------------------

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

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.

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

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
----------------------------------------------------------------------------

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

No problem.

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

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