2003 Limitation?

Rogerisit

Board Regular
Joined
Oct 20, 2016
Messages
53
=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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Oct 20, 2016
Messages
53
=SUMPRODUCT((Attendance!A6:A381<=Bob!A5)*(Attendance!A6:A381>=Bob!A5-6)*(1+ISNUMBER(SEARCH("A",Attendance!B6:B381))/2)
 

Rogerisit

Board Regular
Joined
Oct 20, 2016
Messages
53

ADVERTISEMENT

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
Joined
Feb 16, 2013
Messages
6,723
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​
AAAaAaaaa
8​
Search
1​
1​
1​
9​
Find
1​
2​
#VALUE!​
B8=SEARCH("A",B7,1)
B9=FIND("A",B7,1)
 

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

=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.

I believe that was your intent. If not, please explain your intent.

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
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
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))))))
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,143

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
=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))

 

Forum statistics

Threads
1,137,151
Messages
5,679,895
Members
419,861
Latest member
AceDaMace

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top