# 2003 Limitation?

#### Rogerisit

##### Board Regular
=SUMPRODUCT(--(Attendance!A6:A381<=Bob!A5),--(Attendance!A6:A381>=Bob!A5-6),--(Attendance!B6:B381=Attendance!\$D\$2))+(SUMPRODUCT(--(Attendance!A6:A381<=Bob!A5),--(Attendance!A6:A381>=Bob!A5-6),--(Attendance!B6:B381="?/Attendance!d2"))*0.5)+(SUMPRODUCT(--(Attendance!A6:A381<=Bob!A5),--(Attendance!A6:A381>=Bob!A5-6),--(Attendance!B6:B381="Attendance!d2/?")*0.5))

I am working with 2003 hence many limitations. This formula works except for "Attendance!d2/?" & "?/Attendance!d2" which returns no value. (This is where an employee has a half day such as A/P or S/A (Annual, Sick, Present) In D2 we have the "A.")

I know that "Attendance!d2/?" works in a countif, so I'm wondering if this is just a 2003 limitation or are you able to help with how this can be rectified.

Thanks kindly

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### joeu2004

##### Banned user
I am working with 2003 hence many limitations. This formula works except for "Attendance!d2/?" & "?/Attendance!d2" which returns no value. (This is where an employee has a half day such as A/P or S/A (Annual, Sick, Present) In D2 we have the "A.")

I know that "Attendance!d2/?" works in a countif, so I'm wondering if this is just a 2003 limitation

It is not a limitation of Excel 2003. First, "?" and "*" are recognized as wildcards only in certain functions like COUNTIF; not in regular comparisons. Second, "Attendance!D2/?" would not work, even with COUNTIF, because you are quoting the cell reference.

It might be sufficient to know if D2 contains the letter A. In that case, try:

Code:
``````=SUMPRODUCT(--(Attendance!A6:A381<=Bob!A5),--(Attendance!A6:A381>=Bob!A5-6),1+ISNUMBER(SEARCH("A",Attendance!B6:B381)))
or
=SUMPRODUCT((Attendance!A6:A381<=Bob!A5)*(Attendance!A6:A381>=Bob!A5-6)*(1+ISNUMBER(SEARCH("A",Attendance!B6:B381)))``````

Note: SEARCH is not case-sensitive. It will match "a" as well as "A". Use FIND if you want to make a distinction.

#### joeu2004

##### Banned user
Errata, to late to edit....

Rich (BB code):
``````=SUMPRODUCT(--(Attendance!A6:A381<=Bob!A5),--(Attendance!A6:A381>=Bob!A5-6),1+ISNUMBER(SEARCH("A",Attendance!B6:B381))/2)
or
=SUMPRODUCT((Attendance!A6:A381<=Bob!A5)*(Attendance!A6:A381>=Bob!A5-6)*(1+ISNUMBER(SEARCH("A",Attendance!B6:B381))/2)``````

#### Rogerisit

##### Board Regular
=SUMPRODUCT((Attendance!A6:A381<=Bob!A5)*(Attendance!A6:A381>=Bob!A5-6)*(1+ISNUMBER(SEARCH("A",Attendance!B6:B381))/2)

#### Rogerisit

##### Board Regular
Thank you kindly.
You are quite correct, i did change it to "a" in countif, now that I look back. Your formula nearly works, it has found an additional "A"s being the one's in the date list (attendance!a6:A381) rather than finding only the a's in column b.
Thanks kindly, I will have a play.

#### FDibbins

##### Well-known Member
If you are searching for case-specific letters, then use FIND, not SEARCH, FIND is case-specific, search is not.

In the sample below, both formulas are searching for A (upper-case)...
 A​ B​ C​ D​ 7​ AAA aAa aaa 8​ Search 1​ 1​ 1​ 9​ Find 1​ 2​ #VALUE!​
B8=SEARCH("A",B7,1)
B9=FIND("A",B7,1)

#### joeu2004

##### Banned user
=SUMPRODUCT((Attendance!A6:A381<=Bob!A5)*(Attendance!A6:A381>=Bob!A5-6)
*(1+ISNUMBER(SEARCH("A",Attendance!B6:B381))/2)
Your formula nearly works, it has found an additional "A"s being the one's in the date list (attendance!a6:A381) rather than finding only the a's in column b.

Only by coincidence or if you mistyped the SEARCH range as A6:B381.

My SEARCH range only looks at B6:B381. It is not possible that it found an "A" in other range.

Hypothetically, my SUMPRODUCT might include a row with "A" in column A; but only if there is also an "A" in column B.

However, I think even that is not possible because the assumption of your comparisons with Bob!A5 is that column A is numeric. If it were text, the "<=" comparison would return FALSE, even if Bob!A5 were numeric text.

-----

More importantly, I think I misinterpreted the intent of your original formula, and my formula is incorrect for other reasons.

I think the following might work for you.

Code:
``````=SUMPRODUCT((Attendance!A6:A381<=Bob!A5)*(Attendance!A6:A381>=Bob!A5-6)
* ( Attendance!B6:B381=Attendance!D2
+ ISNUMBER(SEARCH(Attendance!D2 & "/?", Attendance!B6:B381))/2
+ ISNUMBER(SEARCH("?/" & Attendance!D2, Attendance!B6:B381))/2 ))``````

Add absolute references ("\$") appropriately. Your original use of relative and absolute references is inconsistent, IMHO.

Where the column A condition is true (between Bob!A5-6 and Bob!A5), that formula is intended to count 1 if B equals D2 or count 0.5 if B matches D2 preceded by a character and slash or followed by a slash and character.

Note that the formula also counts 1 (0.5 twice) if B matches D2 that is both preceded by a character and slash and followed by a slash and character.

Your formula does the same thing. So I presume that either that is acceptable or, more likely, you assume that the conditions are mutually exclusive. Alternatively, perhaps that is also an error in your original formula.

However, my formula also counts 0.5 if there is more than one character before or after the slash.

If that cannot happen or that is acceptable, great!

But if it can happen and your intent is to count that as zero, as your formula does, please let me know. I can offer a more complex formula that counts that condition correctly.

#### joeu2004

##### Banned user
Errata....
Note that the formula also counts 1 (0.5 twice) if B matches D2 that is both preceded by a character and slash and followed by a slash and character. [....] Your formula does the same thing.

Yours does not! Mea culpla!

However, my formula also counts 0.5 if there is more than one character before or after the slash.
[....]
But if it can happen and your intent is to count that as zero, as your formula does, please let me know. I can offer a more complex formula that counts that condition correctly.

Oh, what the heck....

Code:
``````=SUMPRODUCT((Attendance!A6:A381<=Bob!A5)*(Attendance!A6:A381>=Bob!A5-6)
*( Attendance!B6:B381=Attendance!D2
+ (LEFT(Attendance!B6:B381,LEN(Attendance!B6:B381)-1)=Attendance!D2 & "/")/2
+ (RIGHT(Attendance!B6:B381,LEN(Attendance!B6:B381)-1)="/" & Attendance!D2)/2 ))``````

Arguably, the following formula is more efficient. Array-enter (press ctrl+shift+Enter, not just Enter) the following:

Code:
``````=SUM(IF(Attendance!A6:A381<=Bob!A5, IF(Attendance!A6:A381>=Bob!A5-6,
IF(Attendance!B6:B381=Attendance!D2, 1,
IF(LEFT(Attendance!B6:B381,LEN(Attendance!B6:B381)-1)=Attendance!D2 & "/", 0.5,
IF(RIGHT(Attendance!B6:B381,LEN(Attendance!B6:B381)-1)="/" & Attendance!D2, 0.5))))))``````

#### joeu2004

##### Banned user
=SUMPRODUCT(--(\$G\$5:\$AK\$5<=\$E\$2),--(\$G\$5:\$AK\$5>=\$E\$2-6),--ISNUMBER(SEARCH(B\$3,\$G6:\$AK6)),IF(ISNUMBER(SEARCH("/",\$G6:\$AK6)),0.5,1))
confirmed with Control+Shift+Enter.

That's not precisely the same logic. But if it is sufficient, we can avoid array-entering the formula with the following paradigm:

Rich (BB code):
``````=SUMPRODUCT(--(\$G\$5:\$AK\$5<=\$E\$2),--(\$G\$5:\$AK\$5>=\$E\$2-6),
--ISNUMBER(SEARCH(B\$3,\$G6:\$AK6)), 0.5  + (ISNUMBER(SEARCH("/",\$G6:\$AK6))=FALSE)*0.5)

or

=SUMPRODUCT((\$G\$5:\$AK\$5<=\$E\$2)*(\$G\$5:\$AK\$5>=\$E\$2-6)
* ISNUMBER(SEARCH(B\$3,\$G6:\$AK6)) * (0.5  + (ISNUMBER(SEARCH("/",\$G6:\$AK6))=FALSE)*0.5))
``````

Replies
1
Views
690
Replies
1
Views
713
Replies
2
Views
6K
Replies
2
Views
148
Replies
3
Views
472

1,171,686
Messages
5,876,893
Members
433,217
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back