If statement: How to return original value if false?

[spicycurlyfries]

Hi there,

First posting here -- this forum has been a huge help in the past!

A1 = constant
B1 = ever increasing sumifs based on daily web query & between certain dates
C1 = if(B1<=A1,sumifs(based on daily web query & between certain dates), <leave the="" c1="" value="" that="" existed="" before="" b1="" exceeded="" a1=""></leave>)

My goal is to always have B1 <= A1. If it ever exceeds A1, I want C1 to stop adding up. Everything I try keeps giving me a circular reference warning. Is there another way around this? Or, is there another way to do this? Can I include any other details to help this make sense?

Your help is greatly appreciated!! Thank you!

 

[Jonmo1]

Welcome to the board.

In C1, try
=MIN(A1,B1)

 

[spicycurlyfries]

Welcome to the board.

In C1, try
=MIN(A1,B1)

Hey Jonmo1, does this mean it replaces the current if statement in C1? I need C1 to continue adding a separate portion (it's a piece of the pie from B1). Does that make sense? A1 is the total piece of the pie that remains constant, B1 is the grand total constantly adding from the daily web query, and C1 is adding a smaller piece from the daily web query that ultimately (but separately) contributes to B1.

 

[Jonmo1]

Post the formulas you have in B1 and C1
And describe what you want to happen with C1

 

[spicycurlyfries]

Post the formulas you have in B1 and C1
And describe what you want to happen with C1

Post the formulas you have in B1 and C1
And describe what you want to happen with C1

B6 = Name of a Brand
E6 = Start Date
F6 = End Date

A1 = Budget
B1 = SUMIFS('Sheet1'!$E:$E,'Sheet1'!$B:$B,">="&$E$6,'Sheet1'!$B:$B,"<="&$F$7,'Sheet1'!$D:$D,$B6)+Sumifs('Sheet2'!....SheetX)
C1= IF($B$1<=$A$1,SUMIFS('Sheet1'!$E:$E,'Sheet1'!$D:$D,$B6,'Sheet1'!$B:$B,">="&$E$6,'Sheet1'!$B:$B,"<="&$F$6))

I want C1 to only add up a portion of the sum from Sheet1
D1 would add up the portion from Sheet 2..

But I want C1 to stop adding when the Total from B1 has exceeded the budget, from A1.

Does that make sense? Thank you!!

 

[Jonmo1]

But I want C1 to stop adding when the Total from B1 has exceeded the budget, from A1.

Do you mean
if B1 IS <= A1, then show the result of B1
if B1 is > A1, then only show the value of the sumif UP TO the value of A1

Then C1 is
=MIN(A1,B1)

The min function simply returns the smallest referenced number.
Whichever number (a1 or b1) is smaller.

 

[spicycurlyfries]

Do you mean
if B1 IS <= A1, then show the result of B1
if B1 is > A1, then only show the value of the sumif UP TO the value of A1

Then C1 is
=MIN(A1,B1)

The min function simply returns the smallest referenced number.
Whichever number (a1 or b1) is smaller.

C1 will be a different number than B1, so I need C1 to be whatever the calculated sum was before the entire total (B1) would have met the budget A1. That doesn't necessarily mean that the sum of C1 would = the sum from B1, because there are other columns adding up to B1. (e.g. C1+D1+E1=B1)

 

[Jonmo1]

Sorry, this is still not clear.

Can you post some example VALUES in the related cells, and the results you would expect?

See my signature for a link to Excel Jeanie, this will help you post sample tables.