Sum values in column A ONLY IF the value of the neighbouring column B is a certain value

[watsonman88]

Hi folks,

I'm very inexperienced with Excel and have hit a bit of a wall with some calculations;

Column A contains £numbers and Column B contains a letter (either Y or N)

I want to be able to sum the contents of Column A only if the letter in column B is an N. I have looked at SUMIF, nested IF statements and just SUM so far but nothing appears to be working. The closest I have got so far is with this;

=SUMIF(A1:A20, B1:B20, "N")

This returns a value of 0 (all the other attempts just give me #VALUE) but shouldn't as every cell has a number greater than 0. I'm not sure if it's relevant but I will mention it anyway, the numbers in Column A are the result of another formula/expression, specifically ='Accounts'!F4 for example. Could you possibly shed some light on where I could be going wrong?

Thank you,
Adam

[Glenn Kennedy]

=SUMIF(B:B,"N",A:A) will do it. the syntax of sumif is (criteria range, criteria, sum range)

[FDibbins]

Just to add in - if a formula references another formula, it generally takes teh displayed result, not the actual cell contents (the formula IN the cell)

[watsonman88]

Hi Glenn,

Many thanks for such a quick response. Unfortunately the issue doesn't seem to have been resolved. I have amended the structure of the formula as requested but it still returns a value of 0. Can text format settings interfere with a formula? The column I want SUM'ed is set as Currency? Also the column/cells that contain the "Y" and "N" have been populated via the use of a formula; =IF(Accounts!M7="Paid", "Y", "N") for example.

Thanks

[Glenn Kennedy]

Can you post a sample sheet. go to "Go Advanced" / "Manage attachments".

[watsonman88]

Spreadsheet attached.

Thanks

[FDibbins]

Can text format settings interfere with a formula? The column I want SUM'ed is set as Currency?

if the currency column is text looking like a number, then yes that would cause it to fail.
Test with =isnumber(cell-ref)

Also the column/cells that contain the "Y" and "N" have been populated via the use of a formula; =IF(Accounts!M7="Paid", "Y", "N") for example.

see my post # 3

[watsonman88]

Ah Okay, so the formula reliant on another formula thing shouldn't really pose a problem.

I will check the formatting as well to ensure it's not a silly mistake.

Thank you

[Glenn Kennedy]

For Monthly front sheet for (e.g. May : =SUMIF(F:F,"N",E:E)

[FDibbins]

Correct, it does not matter if the cell being referenced contains a manual entry or a formula, excel works on what is displayed in the cell

[Glenn Kennedy]

Criteria range - where you expect to find the criteria (N in your case),
Criterion - N
Sum range - the cells that meet the criterion to be summed.

My guess is that you swapped the first and last around.

[Glenn Kennedy]

I finally located where you had tried the formula. the problem was that in April there are no amounts that have an N against them. Therefore the total is 0.00

[watsonman88]

Excellent problem solved!

A little embarrassing however the formula given worked all along, the values it omitted would have always returned a value of 0 for the month I was testing. I wanted it to show bills that are outstanding, the month I was checking doesn't have any....

None the less, thank you both for your advice.

Thanks,
Adam

[Glenn Kennedy]

One thing is for sure. you won't make that mistake again. But there are plenty of other mistakes to make!!!

[FDibbins]

You can say that again, Glenn lol