Yet more Excel - exclude certain cells in a sum

[Bleek]

I literally have two columns, Column A has a drop-down box with 'ok' or 'ignore'.

The column to the right column B, simply has a number.

I want to be able to add all these numbers in column B ONLY if column A says 'ok'.

I tried googling it but trying to word it right seem a chore, didn't find much to help me?

 

[HeX]

SUMIF( range, criteria, sum_range )

range is the range of cells that you want to apply the criteria against.

criteria is used to determine which cells to add.

sum_range are the cells to sum.



So for you it'd be something like:

SUMIF(A1:A10, "ok", B1:B10)

 

[Bleek]

Thank you!

 

[Aod]

SUMIF( range, criteria, sum_range )

range is the range of cells that you want to apply the criteria against.

criteria is used to determine which cells to add.

sum_range are the cells to sum.



So for you it'd be something like:

SUMIF(A1:A10, "ok", B1:B10)

I honestly didn't know that function existed, that's very useful. Awesome!

 

[Bleek]

Can you use the SUMIF with a COUNTIF?

For example I have this formula that counts only 'W'

=COUNTIF($R$5:R44,"W")

So could you combine that with =SUMIF(A1:A10, "ok", B1:B10)

i.e. only count the 'W' and do the sum if you have an 'ok' too?

 

[HeX]

Let me just check I get what you are wanting to do.

Say you have 3 columns:

CHECK, NUMBER, LETTER


CHECK can be "OK" or "Ignore".

NUMBER can be any number.

LETTER can be any letter.


And for examples sake we will say there are 8 rows of data filled.


You want to SUM only the NUMBERS that are both OK (in CHECK) and W (in LETTER).

Correct?


In that case you would want to use DSUM.


Make a new sheet (say Sheet 2 in the same workbook). Copy the headers of your main sheet into the new Sheet 2 (they need to be exactly the same).

Under the headers enter the criteria you want to pull data from.

So in the CHECK column type OK. In the LETTER column type W.

Now that we have the rules set up go back to your main sheet.


Go to the cell you want to put the formula in.


You would need to type:


DSUM(A1:C9, B1, Sheet2!A1:C2)


This formula selects your entire sheet (A1:C9), selects the data under B1 to be SUM'd, and selects the criteria you set in Sheet 2.



e.g.

Sheet1

Sheet2

Hope that makes sense


Or the easier way would be to use Pivot tables.

 

[Bleek]

I don't need the B1 aspect, just the check for 'ok' and 'w'.

I need to count how many times an ok and w fall in the same row, if that makes sense?

And thank you for your excellent reply and in taking the time to help - much appreciated!

 

[Bleek]

I don't need the B1 aspect, just the check for 'ok' and 'w'.

I need to count how many times an ok and w fall in the same row, if that makes sense?

And thank you for your excellent reply and in taking the time to help - much appreciated!

Anyone, I still can't work it out from the above example and the 2002 help file is a bit limited.

Perhaps it's time for an upgrade!

 

[Makhaira]

Related to this, but another useful function is =SUBTOTAL(109, range) which will only sum up the rows showing - i.e. if you use filters to cut down your table, it will automatically show you the total for what you have filtered down to.