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]

If I enter a date in AF4 I need it to count as 1

Instead of this for each part:

[COLOR=#333333](AF4*AC44)[/COLOR]

use this:

IF(AF4>0,AC44,0)

or this shorter version:

((AF4>0)*AC44)

 

[oxicottin]

Thank You!

 

[Joe4]

You are welcome!

 

[Rick Rothstein]

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?

If you could have a lot more than 4 cells to perform this calculation with, you can use this formula instead of adding each individually calculated cell together...

=100*SUMPRODUCT(AC4:AC7*(AF4:AF7>0))

just change the both 7s to the actual row number of the last cell to be included in the calculation.

 

[oxicottin]

Hello, I had the formula working but I changed my Worksheet and redid the formula now im getting an Value Not available Error. The formula im using is:

=100*SUMPRODUCT(AP22:AQ45*(D5:H16>0))

The range AP22:AQ45 is formatted to percentage with a decimal at 1 place and the range D5:H16 range is a Date range. I would post/attach the .xls file but I don't know how in this forum?

Thanks,

 

[Joe4]

The two ranges you are multiplying together must be the same size.
Note in the original formula:
AC4:AC7 is four cells
AF4:AF7 is four cells

In your new equation, AP22:AQ45 and D5:H16 are not similarly sized ranges.

 

[oxicottin]

I see I have some merged... Thanks!

 

[Joe4]

I see I have some merged... Thanks!

Ooohhhh... "Merged cells" is a four-letter word around here!
They are very, very bad, in that they cause all sorts of issues, and it is strongly recommended that you remove them, if possible.
If you are simply merging cells across single rows, you can get the same visual effect without all the issues by using the "Center Across Selection" formatting option instead.
See: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

 

[oxicottin]

No I removed the merged cells and am still getting the error here is the new formula...

=100*SUMPRODUCT(AP22:AQ33*(D5:H16>0))

NVM, I got it ... I was another merged cell.

Thanks!