Detectiveclem
Active Member
- Joined
- May 31, 2014
- Messages
- 320
- Office Version
- 365
- 2016
- Platform
- Windows
- MacOS
Hello,<o></o>
I have set up a successful SUMIFS using two criteria toprovide a total figure depending on the criteria selected from the drop-downlist for both criteria (=SUMIFS(B2:B40,A2:A40,F40,C2:C40,G40). <o></o>
Range B2:B40 contains number values<o></o>
Range A2:A40 contains any of the following; Complete,Awaits, On Going, Issue.<o></o>
Range C2:C40 contains any of the following; Team A, Team B,Team C, Team D<o></o>
Cell F40 (Contains the following drop down text: - Complete,Awaits, On Going, Issue). <o></o>
Cell G40 (Contains the following drop down text: - Team A,Team B, Team C, Team D)<o></o>
All relatively straight forward so far, however I want tohave the ability of including the word ALL in both drop-down lists, which wouldthen include all the criteria field data. <o></o>
I.E. If Criteria 1 is selected to ALL and Criteria 2 is TeamC then the result will show the total of all fields for Team C only. And soforth.<o></o>
However as none on the data range includes the text ‘ALL’ I obviouslyget a zero result. Is there any way I can achieve this by changing the formulaor by using wildcards, or by some other means?<o></o>
Grateful for any assistance in this matter. <o></o>
I have set up a successful SUMIFS using two criteria toprovide a total figure depending on the criteria selected from the drop-downlist for both criteria (=SUMIFS(B2:B40,A2:A40,F40,C2:C40,G40). <o></o>
Range B2:B40 contains number values<o></o>
Range A2:A40 contains any of the following; Complete,Awaits, On Going, Issue.<o></o>
Range C2:C40 contains any of the following; Team A, Team B,Team C, Team D<o></o>
Cell F40 (Contains the following drop down text: - Complete,Awaits, On Going, Issue). <o></o>
Cell G40 (Contains the following drop down text: - Team A,Team B, Team C, Team D)<o></o>
All relatively straight forward so far, however I want tohave the ability of including the word ALL in both drop-down lists, which wouldthen include all the criteria field data. <o></o>
I.E. If Criteria 1 is selected to ALL and Criteria 2 is TeamC then the result will show the total of all fields for Team C only. And soforth.<o></o>
However as none on the data range includes the text ‘ALL’ I obviouslyget a zero result. Is there any way I can achieve this by changing the formulaor by using wildcards, or by some other means?<o></o>
Grateful for any assistance in this matter. <o></o>