Using if statement to zero out a specific cell

[wondmand]

Hello,
I am trying to have a cell revert back to the number zero once the letter "Y" has been entered into another cell. For example, In B1 I have a very long "sumif" statement that searches for data below based on specific text in A1. It then returns the given total. In C1 I have a data validation that only allows the user to select the letter "Y". What I need is say the value in B1 has summed the total to 556. When the letter "Y" is chosen I need the value to change back to zero and start counting again from zero. See below

[TABLE="width: 95"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD="colspan: 5"][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]35. Uses simple ="specific cell"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sumif total. i.e 556. Total needs to read zero becuase "Y" was typed in below[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data validation. Only choice is "Y". i.e "Y" is chosen [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="colspan: 5"][/TD]
[/TR]
</tbody>[/TABLE]

Additionally, I have 5 columns from which the letter "Y" can be chosen which all relate to the same "sumif" total. So essentially each time the letter "Y" has been selected the total needs zero out and count again. Is this even possible or do I need a function placed into VBA?

 

[Sinon]

For a simple statement, you can use =IF(C1="Y",0,SUMIF(...)). To look in multiple cells for the "Y", you can add an OR function =IF(OR(C1="Y",D1,"Y"),0,SUMIF(..)). You can also use a countif in the range =IF(COUNTIF(C1:D1,"Y")>0,0,SUMIF(..)).
However, you mention restarting the count as well. What do you mean by that? Count what and where and how would this count be different from the previous count which resulted in 556? Please provide the count formula you are using and a sample data with a manually input desired result.

 

[wondmand]

Is there anyway I can upload my worksheet? It would be much easier.

 

[Sinon]

You can upload it on dropbox (if you have an account) or any other cloud and just share the link. Or you can use a html maker like Excel Jeanie so you can get the code to post on the forum.

 

[wondmand]

https://www.dropbox.com/s/9psznqzag0df3sh/AF Form 2434 v1 3 AMMO COUNT TEST.xlsx?dl=0

Sorry it took so long. When you open it up you will see different sheets. The main one I use is identical except it has sheets titled 1-31. These all represent days in a month. For the example only one is provided. The one I am looking for help on is titled Rounds Count. In rows 3, 10, and 17 is where my sumif statement is and arguments are all defined names for simplicity. The number above represents an Aircraft Tail Number. For example, in B2 you will see the number 35. This is pulled from the data sheet. In B3 ("Total Exp Rows") it is looking below for the number in B2 or H2 respectively then adding the number directly to the right. In the example it is only counting days one and two. Below that you will see will see where the letter "Y" will be inserted. Then below that is a type of inspection. What I need is once a "Y" is selected in say B4 to change the number in B3 back to zero, then start counting again from zero. For example, under Day 1 next to 35 there were 500 Exp. If I enter "Y" into B4 I need the number in B3 to change to zero until I enter in more data below. Then once I enter data into day two it starts counting. The day columns have changed and I now use an "=index" formula to pull data but it shouldn't effect anything. I hope this makes since.