COUNTIF #VALUE (2003 SP3)

[steveh]

Morning all

I have a COUNTIF formula =COUNTIF(I7:AM7,"H")-SUMPRODUCT(--(EXACT(I7:AM7,"h")))*0.5 which is returning a value error but I know that L7:O7 & R7:U7 each contain an H so the count should be 8. Can anybody see a problem with the formula?

[StuartR]

Try putting a wildcard * character after the H, so that you replace "H" with "H*"

[steveh]

Try putting a wildcard * character after the H, so that you replace "H" with "H*"

Hi Stuart

Thanks for the input but adding the wildcard made no difference, what is strange that I use the formula in another workbook for a different range of cells and it works perfectly but even copying the working formula into word and simply changing the cell refrences does not get it too work in this WB?

[rory]

Do you have any error values in I7:AM7?

[steveh]

Do you have any error values in I7:AM7?

Hi Rory

I have tried it with and without values and it is the same, I have however had a Google around and found an array formula that seems to be working =SUM(IF(EXACT(I7:AM7,"H"),1,IF(EXACT(I7:AM7,"h"),0.5)))

[rory]

Your original formula works fine for me. The only way I can get it to return a #VALUE error, is if there is a #VALUE error somewhere in I7:AM7.

[HansV]

Your original formula should only return #VALUE if, as Rory suggested, the range I7:AM7 contains at least one cell with #VALUE.

[steveh]

Do you have any error values in I7:AM7?

Hi Rory

I have tried it with and without values and it is the same, I have however had a Google around and found an array formula that seems to be working =SUM(IF(EXACT(I7:AM7,"H"),1,IF(EXACT(I7:AM7,"h"),0.5)))

Mm I tried copying it down the page and now all I get is the value error again, even if I go back to the single cell I was working on and put the array formula in it shows an error, back to the old drawing board

[steveh]

Your original formula should only return #VALUE if, as Rory suggested, the range I7:AM7 contains at least one cell with #VALUE.

Hi Hans

Sorry I misread Rory's reply, all of the cells in the range contain a #value error until the user form is invoked which then places the H in the selected (date) cells. I will try and rewrite the formula to somehow accept and ignore the cells with the error?

[HansV]

You wrote that you already have found a formula that works, so there is no real need.

[steveh]

You wrote that you already have found a formula that works, so there is no real need.

Hi Hans

Yes I did, but I did post again to say that for some reason that I cannopt understand it stopped working when I tried to drag it down through a number of cells. However I have found a solution in this formula =SUMPRODUCT((ISNUMBER(FIND({"H";"h"},I7:AM7)))*{1;0.5}) but could you kindly advise where I can use EXACT because in my WS some cells have PH due to public holidays and this formula counts the H within it. I have tried to Google and I have tried EXACT in 3 different places but I can't seem to get it work.

[HansV]

This array formula (confirm with Ctrl+Shift+Enter instead of just Enter) should handle it correctly:

=SUM(IF(ISERROR(EXACT({"H";"h"},I7:AM7)),FALSE,EXACT({"H";"h"},I7:AM7))*{1;0.5})

[steveh]

This array formula (confirm with Ctrl+Shift+Enter instead of just Enter) should handle it correctly:

=SUM(IF(ISERROR(EXACT({"H";"h"},I7:AM7)),FALSE,EXACT({"H";"h"},I7:AM7))*{1;0.5})

Thank you for the answer and your patience Hans it worked a treat

[steveh]

This array formula (confirm with Ctrl+Shift+Enter instead of just Enter) should handle it correctly:

=SUM(IF(ISERROR(EXACT({"H";"h"},I7:AM7)),FALSE,EXACT({"H";"h"},I7:AM7))*{1;0.5})

Hi Hans

Sorry to be a pain but I have now tried to implement this code on 61 worksheets for 11 different types of leave code but when I confirm with Ctrl+Shift+Enter all 11 cells on 61 worksheets show #NA

In the example Gif below you can see that this Employee has a number of 'M' days off (Maternity, 231 to be precise) and in the Maternity cell is the formula:-

=SUM(IF(ISERROR(EXACT({"M";"m"},$D$11:$AH$22)),FALSE,EXACT({"M";"m"},$D$11:$AH$22))*{1;0.5})

I would at a guess think that I have somehow deployed the code incorrectly but I wonder if you can see what it may be (BTW behind each cell is a formula =Planner!xx which produces a #Value eroor which in turn is hidden with CF)

[HansV]

Please attach your workbook again...

[steveh]

Please attach your workbook again...

Hi Hans

I will try and emulate a workbook tonight that meets the size requirements, in the actual workbook the employee summary takes its values from about 320 cells dotted over different month view calendars, Ie. D11:AH31 takes its value from the January worksheet and D12:AH29 from the February worksheet etc.

[steveh]

Please attach your workbook again...

Hi Hans

I will try and emulate a workbook tonight that meets the size requirements, in the actual workbook the employee summary takes its values from about 320 cells dotted over different month view calendars, Ie. D11:AH31 takes its value from the January worksheet and D12:AH29 from the February worksheet etc.

Hi Hans

Typically I imagined that I had to recreate 9mb into <100kb and went into a blind panic

Please find attached the earlier workbook with a summary page inserted that looks as though it recreates the problem, in reality as previously mentioned the data would not be extracted from 4 contiguous rows but from 12 worksheets. In this example there has been some CF removed from the planner to get it below 100kb but I do not think it changes the problem.

[HansV]

OK, I see now. In your first post, you stated that you wanted to sum over 1 (one) row: I7:AM7. The formula works perfectly for that, but it can't be expanded to multiple rows. So you have to sum by row first, and then separately sum over the rows.

See the attached version.

2011-LeavePlanner(a).xls