Forum Discussion
How do I count cells based on column headers and cell value
I have a table where each column has two headers (a header and subheader if you will).
I need a formula that will count the number of 'G' values in rows 3 to 6 if the value in the top row is En and the value in the second row is E.
I thought I'd need to use COUNTIFS, doing something like
=COUNTIFS($A$1:$H$1,"En",'$A$2:$H$2,"E",$C$1:$H$6,"G")
But I keep getting a #VALUE error :-(
Can some kindly soul help out?
As variant
=SUMPRODUCT( ($J$3:$Q$6="G") * ($J$1:$Q$1 = "En") * ($J$2:$Q$2 = "E") )For COUNTIFS() ranges shall be of the same size.
try:
=SUMPRODUCT((A3:H6="G")*(A2:H2="E")*(A1:H1="En"))=LET( a,FILTER(A3:H6,(A1:H1&"|"&A2:H2)="En|E"), b,COUNTA(FILTER(a,a="G")), c,IFERROR(b,"no match"), c)
