SUMPRODUCT Formula results on #VALUE

[domzki225]

Hi all!

I have a little problem here with a file I am working. On my workbook I have 6 sheets:
Sched, CM Report, Using Held Calls, Using Lunch, Held Calls and Lunch sheets.

On the Using Held Sheet and Using Lunch Sheet I have the names of my agents on Col A and on Col B-H are specific dates. For Cells B3:H15 I inserted a formula. Here is an example of the formula:

=IF(Sched!B3="y",SUMPRODUCT(('Held Calls'!$A$2:$A$343=$A3)*('Held Calls'!$B$2:$B$343=B$2)*(('Held Calls'!$C$2:$C$343))),"RD")

This formula works perfectly for Held Calls but on my Using Lunch it results to #VALUE. I have tried everything I know to have this resolved but I am stomped!

I attached here my worksheet to get a better picture of my issue if I failed totally in explaining it.

Thanks!

[Debraj Roy]

I think.. you know.. that..

In CM Report sheet, Column O (Contain data in Held Cell) are all Numeric..
but In CM Report sheet, Column R(Contain data in Lunch Cell) are all few of them Time, and lots of them are just TEXT....

still try this,,

in B3

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Format cells as HH:MM:SS if required,..

[Pepe Le Mokko]

@Debraj What is the need for a array formula? SUMPRODUCT does the job by itself

@Domzki In the CM report sheet add a helper column and enter =IF(LEFT(R2,1)=":",("0"&R2)+0,R2)
Pull down as needed and copy paste special - values over column R ( format if needed).
All should be well

You have the same problem in other columns :59:02 is text as Debraj pointed out. A 0 should be added before the text value

[Norie]

You don't appear to have times in column C on sheet 'Lunch'.

If I copy this formula down from D2 on 'Lunch',

Please Login or Register  to view this content.

then copy the results and paste special values to column C the formulas on 'Lunch Held' work, well they don't return #VALUE! anymore.

[Debraj Roy]

Hi Pepe Le Mokko!

Thanks for the catch.. however.. in few case .. SUMPRODUCT required CSE.. to evaluate.. highlighted ones..


=IF(Sched!B3="y",SUMPRODUCT((Lunch!$A$2:$A$343=$A3)*(Lunch!$B$2:$B$343=B$2)*((IFERROR(TIMEVALUE(0&Lunch!$C$2:$C$343),Lunch!$C$2:$C$343)))),"RD")

[domzki225]

THANKS FOR THE RESPONSE GUYS!

Fully appreciate it....

Tried every suggestion, all worked.... =D

[Debraj Roy]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.