Request for an Equation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StrontiumDog67 - 11 Mar 2008 15:42 GMT

I'm trying to generate an equation to calculate the total value of costs in
one column, if there status value in another column is equal to 100 (or it
could be a string value of "Confirmed" - the value 100 is arbitary).

So it's Consolidate the Values in Column A if the corresponding Value in
Column B=100 and provide a SUM total in Column C.

Not sure if this would be a Conditional Formula but hope someone can help.

regards

adrian

Reply to this Message

Pete_UK - 11 Mar 2008 15:55 GMT

Try this:

=SUMIF(B:B,100,A:A)

Hope this helps.

Pete

> I'm trying to generate an equation to calculate the total value of costs
> in
[quoted text clipped - 9 lines]
>
> adrian

Reply to this Message

StrontiumDog67 - 11 Mar 2008 16:18 GMT

Thanks Pete. Unfortunately it didn't work, it reports a value of zero.

I think the problem I may not have explained fully is that I have a
Value/State in Column B of either 100,90,60,30 or 10 and I'm trying to get a
SUM TOTAL of the Sales Which have a 100(%) chance of being won.

Job 1 in Row 1 has a Sale Value of £300 in Column A and has been assigned a
Sale Chance of 100 in Column B.
There are 200 jobs (each on a seperate row) - the jobs have a different
Value/State depending on whether they are likely to be won.

I'm trying to calculate in a Summary table, how many jobs have a 100% chance
of occuring. If I have that calculation then I can easily run summaries for
the other values.

> Try this:
>
[quoted text clipped - 17 lines]
> >
> > adrian

Reply to this Message

David Biddulph - 11 Mar 2008 17:32 GMT

If your number in column B is not 100 but 100%, then your formula will need
to change from =SUMIF(B:B,100,A:A)
to =SUMIF(B:B,100%,A:A) or =SUMIF(B:B,1,A:A) [as 100% is equal to 1]

If that doesn't work, make sure you've really got numbers in the relevant
cells, and not text. =ISNUMBER(A2) or =ISTEXT(A2) will tell you what you've
got. Keep an eye open for spaces or other stray non-printing characters.
--
David Biddulph

> Thanks Pete. Unfortunately it didn't work, it reports a value of zero.
>
[quoted text clipped - 40 lines]
>> >
>> > adrian

Reply to this Message

StrontiumDog67 - 11 Mar 2008 18:03 GMT

User Error! User Error!

Got it to work. Stupidly was calling an incorrect column. Here was the end
formula:
=SUMIF($F$12:$F$109,($A$4),I12:I109)

Thanks to you [David] and to Pete_UK.

regards

adrian

> If your number in column B is not 100 but 100%, then your formula will need
> to change from =SUMIF(B:B,100,A:A)
[quoted text clipped - 50 lines]
> >> >
> >> > adrian

Reply to this Message

Pete_UK - 11 Mar 2008 20:09 GMT

Glad you got it to work - thanks for feeding back.

Pete

On Mar 11, 5:03 pm, StrontiumDog67
<StrontiumDo...@discussions.microsoft.com> wrote:

> User Error! User Error!
>
[quoted text clipped - 64 lines]
>
> - Show quoted text -

Reply to this Message

David Biddulph - 11 Mar 2008 16:08 GMT

=SUMIF(B1:B99,100,A1:A99) or =SUMIF(B1:B99,"confirmed",A1:A99) as
appropriate [or even =SUMIF(B:B,"confirmed",A:A) ].
--
David Biddulph

> I'm trying to generate an equation to calculate the total value of costs
> in
[quoted text clipped - 9 lines]
>
> adrian

Reply to this Message