0

I am trying to use a spilled range as criteria in the COUNTIFS function but the result I am getting is a blank spilled range and it's not working the way I want it to.

So I want the code to spit out the corresponding value under the column [Base] if the value under [Originator] and [Beneficiary] matches any of the values in the fixed spilled range $BQ$40#, the value under column [IN/OUT] matches "IN", the value under column [Type] matches any of the values in the fixed array $BW$3:$BW$30 in sheet IP and the value under column [Channel], matches any of the values in the fixed array $BX$3:$BX in sheet IP. Otherwise, it should just leave the cell blank.

I used this code which sits in the sheet PA going from the cell CA30 to CA100.

=IFS(Table23[@[Date]]="","",COUNTIFS(Table23[@[Originator]],$BQ$40#,Table23[@[Beneficiary]],$BQ$40#,Table23[@[IN/OUT]],"IN",Table23[@[Type]],'IP'!$BW$3:$BW$30,Table23[@[Channel]],'IP'!$BX$3:$BX$30),Table23[@[Base]],TRUE,"")

What am I doing wrong here? How do I fix this? Any help will be appreciated. TIA.

Excellent
  • 3
  • 4
  • The inclusion of the @ symbol in your formula means that you are referencing just a single cell within each column of Table23, not the entire column. `Table23[@[Originator]]`, for example, is referring to just one cell within the Originator column (being that corresponding to the row in which your formula lies). – Jos Woolley Jan 04 '22 at 10:06
  • Also, spilled ranges are not permitted within Excel Tables. – Jos Woolley Jan 04 '22 at 10:11
  • That's right. That's what I want. I only that one cell to be checked to see if it matches any value in the corresponding criteria range and spit out the corresponding value under the column [Base]. And the spilled range is outside the table. The formula also sits outside the table. Apologies for not being clear. Thanks. – Excellent Jan 04 '22 at 10:17
  • It still looks like you have your ranges the wrong way round. The spill ranges should be the criteria range, not the criteria, otherwise you will get an array of results for each row, which is not what you appear to want. – Rory Jan 04 '22 at 10:20
  • Yup @Rory That made sense and tried your suggestion and used this `=IFS(Table23[@[Date]]="","",COUNTIFS($BQ$40#, Table23[@[Originator]],$BQ$40#,Table23[@[Beneficiary]],Table23[@[IN/OUT]],"IN",'IP'!$BW$3:$BW$30,Table23[@[Type]],'IP'!$BX$3:$BX$30,Table23[@[Channel]]),Table23[@[Base]],TRUE,"")` but its giving "#VALUE" as the result, even when all the conditions are met. I tried `=IFS(Table23[@[Date]]="","",COUNTIFS($BQ$40#, Table23[@[Originator]],$BQ$40#),Table23[@[Base]],TRUE,"")` to check where the problem lies and it gave me a blank result, again even when all the conditions are met.. – Excellent Jan 04 '22 at 10:55
  • 1
    All criteria ranges passed to COUNTIFS must comprise the same number of rows. – Jos Woolley Jan 04 '22 at 11:05
  • Also, unless Beneficiary and Originator have the same value, your COUNTIFS formula will always return 0. I think you'd be better off showing some sample data, what result you expect, and why. – Rory Jan 04 '22 at 11:12
  • Thanks, guys. It all makes sense. @Rory So the spilled array $BQ$40# will have the names of the people like Tom, John, Joseph, Harry which is retrieved using UNIQUE function it will never be the same. It can be one it can be nine. These individuals can be the originator or the Beneficiary. I don't want my formula to return any value if there is only one value in the spilled array. 'IP'!$BW$3:$BW$30,Table23[@[Type]] holds types of conversations they had like friendly, counselling, tutoring, etc. – Excellent Jan 04 '22 at 11:45
  • Agreed with Rory. Looks like a very small mock dataset with expected results would be useful here. – Jos Woolley Jan 04 '22 at 11:50
  • IP'!$BX$3:$BX$30 will hold the names of channels like a phone call, Video call, Zoom call, etc. [Base] is the total time spent in that interaction. I am trying to calculate the total time they have spent with each other conducting a certain range of conversations using a certain range of channels. Type and Channels are of the same length but the spilled array will never be same to the IP'!$BX$3:$BX$30 and 'IP'!$BW$3:$BW$30 range. The data that I am sorting through contains over 50 different [Types] & [Channels] and even more [Originator] and [Beneficiary]. – Excellent Jan 04 '22 at 11:53
  • Is there any way to make this work? or any other alternate formula? Sorry if all this is too confusing. Thanks again for your help. – Excellent Jan 04 '22 at 11:54
  • So the criteria is that both the Beneficiary and the Originator must appear in the list starting at BQ40#? – Rory Jan 04 '22 at 12:01
  • @Rory Yes, the name of only those individuals that this data is being sorted for will be in that list starting at BQ40# and the criteria is that both the Beneficiary and the Originator must be from this list. Also, the Beneficiary and the Originator in a row are never going to be the same. – Excellent Jan 04 '22 at 12:16
  • Then I'd probably use something like this (untested): `=IF(Table23[@Date]="","",IF(Table23[@[IN/OUT]]<>"IN","",IF(SUMPRODUCT((ISNUMBER(MATCH(Table23[@Originator],$BQ$40:$BQ$42,0)))*(ISNUMBER(MATCH(Table23[@Beneficiary],$BQ$40:$BQ$42,0)))*(ISNUMBER(MATCH(Table23[@Type],IP!$BW$3:$BW$30,0)))*(ISNUMBER(MATCH(Table23[@Channel],IP!$BX$3:$BX$30,0))))>0,Table23[@Base],"")))` or use `FILTER`. – Rory Jan 04 '22 at 12:33
  • @Rory This worked. Thank you. – Excellent Jan 05 '22 at 00:29

0 Answers0