Request for an Equation

[StrontiumDog67]

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

 

[Pete_UK]

Try this:

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

Hope this helps.

Pete

 

[David Biddulph]

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

 

[StrontiumDog67]

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.

 

[David Biddulph]

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.

 

[StrontiumDog67]

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)
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.

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]

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

Pete

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)
 to =SUMIF(B:B,100%,A:A) or =SUMIF(B:B,1,A:A) [as 100% is equal to1]

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.

- Show quoted text -