I am trying to use the IF and SUMIF function on Excel

[kellyyy]

I am wanting to use the value of a date function to set parameters for adding cells together.


43236.00 43267.00 43297.00 43328.00 43359.00 43389.00 43420.00 43450.00 43186.00
2.00 4.00 1.00 5.00 0.00 12.00 1.00 1200.00%
0.00 0.00 0.00 0.00 0.00 0.00 1.00 0.00%

the bold is a number value of a date. 43186 is today's date. Subsequently 43236 is 30 days in future and so on. I am trying to make a formula that will (=if(43200<43206, 2) I can get that to work just fine. But, I cannot get it to read if: =if(43200<43267,2+4) all in the same formula line. In my excel spreadsheet I have:
=IF(U7<G7,G8) Where "2" is G8. I get the correct answer. But if I add on the same line: =IF(U7 < H7, G8 + G9) "4" is G9. I am trying to have U7 display the answer if the criteria is met. I tried to up load the excel but it gives me an error.

 

[Becky]

Welcome to the forum @kellyyy

I'm not sure I follow what you're trying to do... what do you mean by 'if I add on the same line'? Also, if you're trying to get the answer to display in U7, but also want to feature U7 in the formula, you are creating a circular reference.

 

[kellyyy]

Thanks Becky. I had a feeling I did not explain it well. I tried to attach the Excel file but the city has limitations on doing that. With Email it is easier.


T8 or the “T” column is where I want the sum to be at. Currently I have a formula in the T column: =IF(U7>G7,G8). That works great if I wanted only one month. I want to add G8, H8, I8, and J8 sequentially and display in T8 (or T column identified as “Proto,” if it meets the criteria. Each column (G,H,I,J etc.) represents a monthly pay estimate. The date of 43329 represents “today” for the purpose of this example. “43236 represents May 16th for column G, column H is June, and you get the idea.


To explain further, each pay estimate ends on the 16th of the month. The Proto column should sum up only what is paid through the last pay app. The next pay app period is getting updated continuously but is not included in the paid total to date because it will be paid at the end of the next cycle. So the Proto column will only display the Pay estimates through and up to the 16th of the previous month or when ever the current date number is greater than the PE number date.


So, I was trying to write a formula that was in essence: =IF(U7>G7,G8),=IF(U7>H7,G8,H8),=IF(U7>I7,G8+H8+I8),=IF(U7>J7,G8+H8+I8+J8), etc.. So if U7 (current date) is greater than the respective column date, I want it to add it together, 2 + 4 + 1 + 5, etc., and display it in the Proto column “T”. The T column is an audit column where I can compare to other Excel files.

I hope I explained it better this time. Thanks for your help. k

G H I J K ~ T U
6 PE #1 PE #2 PE #3 PE #4 PE #5 ~ Proto
7 43236.00 43267.00 43297.00 43328.00 43359.00 ~ 43329.00
8 2.00 4.00 1.00 5.00 6.00 12

 

[Becky]

Ah ok, I think I understand now. You're looking to do a nested IF function. With any IF function you have this format:

IF(criteria,value if true,value if false)

So with a nested IF function, you have another IF function in the 'value if false' part. There is no need to put '=' each time, it only goes at the start of the formula (unless used in the criteria, for example). So your formula would look like this:

=IF(U7>G7,G8,IF(U7>H7,G8+H8,IF(U7>I7,G8+H8+I8,IF(U7>J7,G8+H8+I8+J8,"Error"))))

I've added "Error" as the final 'value if false' because I'm not sure what you want there - and presumably if none of the criteria are met then something is probably amiss.

Hope this makes sense! Let me know if you have any questions.

 

[kellyyy]

Becky,
thank you. I am good with the error at the end. I can format what I need. I placed the formula in the T8 cell. I manipulated the current date to read in the future beyond August column and then added numbers to test it. It still is only reading the G8 column and not adding the H8, I8, J8 columns together. k

 

[Becky]

Could you do a screenshot of the data?

 

[kellyyy]

 

[Becky]

Ah, sorry, I see the problem. The formula is backwards! As things stand it looks at the first date, which is before the U7 date, and stops there. We need it to work the other way - ie start with the last date and work back. Will those dates always be sequential? If so this should work:

=IF(N7<U7,SUM(G8:N8),IF(M7<U7,SUM(G8:M8),IF(L7<U7,SUM(G8:L8),IF(K7<U7,SUM(G8:K8),IF(J7<U7,SUM(G8:J8),IF(I7<U7,SUM(G8:I8),IF(H7<U7,SUM(G8:H8),IF(G7<U7,G8,"Error"))))))))

There's probably a better way to do this using arrays, but this should work.

It assumes you want to know the cumulative position up to but not including the date in U7. If you want it to include that date, then change each < to <=.

Hope this helps!

 

[kellyyy]

Becky,
it works great and thank you for your help. k