Nested Function & Offset Cell ERROR

Hello:

The following formulas are kicking-out the #VALUE! error. It worked fine on the spreadsheet No. 1 but when I copied and pasted fomula to another spreadsheet with the exact same layout and formatting, I received this error. I thoroughly checked the formula to ensure I did not inadvertantly place a space, etc. but it seems to to be entact.

Is there another function that could be added to trap/catch data type or other errors that are irrelevent?

=SUMPRODUCT(($C$42:$C$61<>"Time:")*E42:E61) and

=SUMPRODUCT(($C$42:$C$61="Time:")*E42:E61)

Column C is text data and column E is the number data.

Thanks a bunch for your assistance.Test Events.xlsx

DDM,

I don't see anything wrong with your formula... just make sure the ranges are the same "size"...

Your attachment with the formulas seems to work on my end... however, it seems to be a different spreadsheet...

=SUMPRODUCT(($C$42:$C$61="Time:")*E42:E61) --> go to the cell where the formula is and click the fx button next to the formula bar and see what your array1 = {#;#;#;#...#} looks like, if it looks like {0;0;0;0;0;0...0} then your "Time:" doesn't = "Time:" but maybe is "Time: " (with a space after or something) make sure it's actually "Time:", change all necessary cells using copy/paste so you don't make a typo

Hello,

I followed all of the above suggestions but still did not work. I have attached a screenshot of what F9 produced. Maybe someone can help.Test Events.xlsx

DDM64, your range is incorrect... your data is from cells B4:B11 and C4:C11 and not C42:C61 and E42:E61...

Change your formula in the conditional formatting to =SUMPRODUCT(($B$4:$B$11="Time:")*C4:C11)

Use this range for the rest of your formulas...

When you first posted you data there were merged cells, I always unmerge (is that right terminology? )the cells when writing formulas.
Pay attention to your another sheet, is there any merged cells.

Hello.

Yes, there are merged cells. Why did it work on the first sheet that also has merged cells and not on the next sheet I applied the formula to? When I get to the office in the morning I will test this by unmerging the 'Time' reference cell. I will test this at home as well and let you know if it worked. Thank you.

Good morning.

I have attached an updated snapshot of the exact formating, etc. that is in my worksheet. Each worksheet is created off of one template, so I do not understand why it works on one and not on the others. Formula was copied and pasted.

I do have Links and Conditional Formating in the cells' E42:E61.

Your help is greatly appreciated. Please see Event Test (2). DeannaTest Events.xlsx

Hello.

I understand that I will receive #VALUE! Error if there are merged cells involved. However, as I stated above, the formula works great on the Template but does not work on the rest of the worksheets (throws a #VALUE! Error) that are a clone of the template. So in theory, it should not work on the Template either.

I have discovered that the following formula will trigger a #VALUE! error:

=SUMPRODUCT(($C$42:$C$61<>"TIME:")*E42:E61)

"One or more cells that are included in a formula contain text, and your formula performs math on those cells by using the standard arithmetic operators (+, -, *, and /).

Instead of using arithmetic operators, use a function, such as SUM, PRODUCT, or QUOTIENT to perform an arithmetic operation on cells that may contain text, and avoid using arithmetic operators in the function. Instead, separate the arguments by using commas."

=SUMPRODUCT(($C$42:$C$61<>"TIME:")*E42:E61)

If I take out the Operator (*) and replace with a comma as suggested, my formula stops triggering the #VALUE! Error but my result is 0. It is not reading the sum portion of the formula. A Syntax issue, I guess.

Please see Event Test (2) for exact replica of my original spreadsheet section that I am having difficulty with.

Can someone please help me find a solution. Thanks a bunch. DeannaTest Events.xlsx

Hi,

I must be going crazy, because I don't see #VALUE! anywhere...

Anyway, have you tried a SUM array-formula?

=SUM(($C$42:$C$61<>"TIME:")*E42:E61)

You must use array-enter (CTRL-SHIFT-ENTER), instead of just ENTER.

It's worth a shot... good luck...

Good afternoon.

As per my above explanation, I have also tried unmerging celles (C42:C61), and tried the CTRL + SHIFT + ENTER and still receiving #VALUE! Error.

I have attached the file with a snapshot reflecting the #VALUE! Error.

Your assistance is greatly approciated.Test Events.xlsx

You can't multiply text values... try this array formula...

=SUM(($C$42:$D$61<>"TIME:")*IF(E42:E61="",0,E42:E61))

Again, use array-enter... CTRL-SHIFT-ENTER

I tried the above but still receiving #VALUE! Error (with CTRL + SHIFT + ENTER).

I understand you cannot use a string in a mathematical equation. The individual who originally assisted me suggested I use:

1) =SUM(($C$42:$D$61<>"TIME:")*IF(E42:E61="",0,E42:E61))

and

2) =SUM(($C$42:$D$61="TIME:")*IF(E42:E61="",0,E42:E61))

My original formula that I started out with:

3) =IF(COUNTIF(E42:E61,">0"),COUNTIF(E42:E61,">0"),"-") NOTE: The problem I encounter with this is it counts the events & the time as an event.

and

4) =IF(SUM(E42:E61),SUM(E42:E61),"-")

Anymore suggestions? Deanna

Hi DDM,

Sorry, a little revision... =SUM(($C$42:$C$61<>"TIME:")*IF(E42:E61="",0,E42:E61))

See my attached version... let me know if you still see the error...

Hell djapigo.

It worked great! You rock!! Thanks a bunch for all of your help. Deanna