Summing the results of nested IFs

[stevejfice]

I know this is going to be something exceptionally simple and it's because I'm doing it via IF's (which technically return either a 0 or 1 for true of false) so I'll probably kick myself when you come back with a totally obvious answer.

I have this formula: =IF(J5="","",IF(C5="","",K5)) which returns either a value of 10p or £1.00 based on the results of the IF's, this formula is copied down for about 70 rows (with the row ref's increasing in each instance of the formula).

For a bit more reference;
Cell J5 is just a yes or no drop down
It checks C5 to ensure it's not blank
and K5 is =IF(C5="","",IF(J5="No","10p","£1.00"))
(I'm beginning to think I've made this too complicated for what it needs to be!)

What I want to do is have one cell that totals all the amounts into one easy to read figure for a sales spreadsheet to help work out our profit margin on a batch of data.

[Fotis1991]

Just an idea.

=IF(C5="","",IF(J5="No",0.10,1))

Format the cell as you want.

Then>>>=SUM(K5:K1000)

[Pete_UK]

Use this formula:

=SUM(K5:K100)

Hope this helps.

Pete

[stevejfice]

Just an idea.

=IF(C5="","",IF(J5="No",0.10,1))

Format the cell as you want.

Then>>>=SUM(K5:K1000)

That works, but I unfortunately need to know the result of each lead's value to us (10p or £1.00) in one cell, then add all of them together later

Use this formula:

=SUM(K5:K100)

Hope this helps.

Pete

That's what I tried and I get a result of 0 as it's not summing values, but results of IF's.

[Fotis1991]

=sumif(j5:j100,1,k5:k100)

=sumif(j5:j100,0.10,k5:k100)

[Pete_UK]

Change this formula =IF(C5="","",IF(J5="No","10p","£1.00")) in K5 to this:

=IF(C5="","",IF(J5="No",0.1,1.0))

then copy down. If you put quotes around numeric values then they become text values, which are treated as zero.

Hope this helps.

Pete

[stevejfice]

Change this formula =IF(C5="","",IF(J5="No","10p","£1.00")) in K5 to this:

=IF(C5="","",IF(J5="No",0.1,1.0))

then copy down. If you put quotes around numeric values then they become text values, which are treated as zero.

Hope this helps.

Pete

Perfect, this worked

[Fotis1991]

..Just curious..

Why the suggestion of post2, didn't work?

Seems same to me with this of post6!

[stevejfice]

Essentially it would have done, it was just the extra formatting which I messed up on as well. Thank you for helping as well.

[Pete_UK]

Perhaps you can mark the thread as solved if you consider it to be so - the FAQ describes how to.

Pete