0

How to get below mentioned google sheet formula to work in EXCEL??

=countif(flatten(filter($C$3:$G$22,$B$3:$B$22=$J$2)),I4)

Reference image of the table and dashboard

1

I combined formula in google sheet to create a dashboard using COUNTIF,FLATTEN, and FILTER. Apparently its not working in Microsoft Excel. As flatten is not there, the alternative formula like TOROW, TOCOL and Transpose didn't seems to work at all.

So how should i approach to get similar result in Excel??

vimuth
  • 5,064
  • 33
  • 79
  • 116
Akhil Visweswar
  • 1
  • Pease have a look [here](https://stackoverflow.com/questions/74034161/trying-to-emulate-my-flatten-function-output-in-excel-on-a-mac) – marikamitsos Mar 19 '23 at 17:06
  • This issue was the same one I was having (https://stackoverflow.com/q/75748647/11861218). The first comment explains how in Excel, the COUNTIF formula only accepts a range for the first criteria, not an array. The answer to this post might help you if you're able to change it to match your needs. – kaitlynmm569 Mar 19 '23 at 17:39

1 Answers1

0

Instead of using a countif, just use a sum formula. It'd also be easier to test this if you posted with Mark down table

=SUM(FILTER(IF($C$3:$G$22=I4,1,0),$B$3:$B$22=$J$2))
pgSystemTester
  • 8,979
  • 2
  • 23
  • 49