Date to count as one in formula

[oxicottin]

If I enter a date in AF4 I need it to count as 1 and I want to multiply it by AC4 and my formula is going to be a few of these adding them
together then I want to *100

Like this...

=((AF4*AC44)+(AF5*AC5)+(AF6*AC6)+(AF7*AC7))*100

But how do I get it to count the date as a number in the AF column?

 

[Joe4]

I see now. I had the image on half my screen, and the AB column was being cut off.

This appears to be a bit tricky. You could certainly do it using "helper" cells to look up each value in column A and return the decimal, and then sum the decimals.
The VLOOKUP for A5 would look like this:

=VLOOKUP(A5,$A$30:$AB$36,28,0)

But to try to lookup and sum all the values in a single formula with no helpers is going to be a bit tricky. Off the top of my head, I am not sure how to do that.
Perhaps this link will help: https://www.ablebits.com/office-addins-blog/2014/08/05/excel-vlookup-sum-sumif/

 

[oxicottin]

I entered your example into A5 and received and Circular Reference Warning

One or mer formulas contain a circular reference and my not calculate correctly....Ect

 

[Joe4]

No, you wouldn't place that formula in cell A5. A5 is where you are entering the Act #. You would place that in some blank column on row 5 (might have to insert a column). You could even hide that column, if you like. Let's say that you put in on D5 and then copy the formula down to cell D24. You could then enter this formula in C25 to get your desired sum:

=SUM(D5:D24)

 

[oxicottin]

Oh ok, can you explain the example because I still am getting errors "Value not available" for each row in the column.

Your example highlights my entire safety activity box "$A$30:$AB$36" except for the Act%. What is the 28 and the 0 for? I followed your instructions and when I enter a number in the Act# or A5 the hidden column I created shows a 0.

 

[oxicottin]

Ok I figured out why it wouldn't work and what the numbers were for.. when I added the hidden column it changed the 28 number.

=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, optionally specify TRUE for approximate match or FALSE for an exact match).

But here's the thing how do I get it to not count if I have reached my max Act Limit for the Activity?

 

[Joe4]

But here's the thing how do I get it to not count if I have reached my max Act Limit for the Activity?

This is getting pretty complex!
Is the limit per day, or just down the entire column?
I am thinking you you might need to incorporate an IF function that uses COUNTIF (or COUNTIFS) to count the number. Or perhaps try to come up with some VBA to do it.

 

[oxicottin]

There's no limit per day you can only perform a safety activity no more than its max... Well you can perform it but you just wont get the % after you reached the max. Maybe I should do this in Access if its to hard in Excel?

 

[Joe4]

What I mean is if there is a limit of 4, is that a limit of 4 per day, or 4 total (doesn't matter if it spans multiple days)?

 

[oxicottin]

Each one row has it's own limit as you see in the column at the bottom of the image with a heading "Activity Limit".Yes it doesn't matter if it spans multiple days ect. You only give a date the activity was done on and they could be weeks apart. We do a new sheet every quarter. Thanks!

 

[Joe4]

OK, assuming that you insert the column so that the 3rd argument of the VLOOKUP changed from 28 to 29, like you experienced, replace that formula with this one (put on row 5 and copy down):

=IF(COUNTIF(A$5:A5,A5)<=VLOOKUP(A5,$A$30:$B$36,2,0),VLOOKUP(A5,$A$30:$AC$36,29,0),0)