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

Try this:

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

Hope this helps.

Pete

"StrontiumDog67" wrote in message
...
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

=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

"StrontiumDog67" wrote in message
...
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

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.

"Pete_UK" wrote:

Try this:

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

Hope this helps.

Pete

"StrontiumDog67" wrote in message
...
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

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

"StrontiumDog67" wrote in message
...
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.

"Pete_UK" wrote:

Try this:

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

Hope this helps.

Pete

"StrontiumDog67" wrote in
message
...
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

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


"David Biddulph" wrote:

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

"StrontiumDog67" wrote in message
...
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.

"Pete_UK" wrote:

Try this:

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

Hope this helps.

Pete

"StrontiumDog67" wrote in
message
...
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

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

Pete

On Mar 11, 5:03*pm, StrontiumDog67
wrote:
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



"David Biddulph" wrote:
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

"StrontiumDog67" wrote in message
...
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.

"Pete_UK" wrote:

Try this:

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

Hope this helps.

Pete

"StrontiumDog67" wrote in
message
...
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- Hide quoted text -

- Show quoted text -