Need help with Countifs that has vertical/horizontal criteria

Hi,

I'd like to do a countif function that returns the number of filled cells for a given sector in columns D, E, and F based on "value" or "policy". For instance, Media has two "value" cells whereas filled telecom only has one. Similarly, Media has one "policy" cell while Telecom has 0. In other words, I'm trying to do a lookup of a sector then do a countif with the horizontal criteria in row 2. An obvious issue is that the array Excel is looking for isn't contiguous with the vertical lookup, and I tried Index/match but that only returns one value...

Sectors	A	B	C	D	E	F
				Value	Policy	Value
Media	11	33	33	33	33	33
Telecom	33	10	33			33
Automotive	33	33	16	33	33

Any help would be very much appreciated!

Try this:

=COUNTIFS($E3:$G3,">"&0,$E$2:$G$2,E$2)

Copy across and down.

Estevaoba, how can that be combined with a vlookup or index/match to find a given Sector then see which “values” contain a value based on that sector?

Maybe this:

=COUNTIFS(OFFSET($E$2:$G$2,MATCH("oil",$A$3:$A$9,0),0),">"&0,$E$2:$G$2,E$2)

And you'll probably want to replace "oil" with a cell reference with data validation.

Estevaoba, that works perfectly!! Thanks a bunch

Glad to help!

Is there any way to do this without the offset function? It seems to be having problems when I copy it down the sheet to look for different sectors...

The formula in post #5 with OFFSET function is to find values for only one given sector, as requested in your post #4.
Maybe if you post an example table with a few more rows to illustrate this problem, it will help us find a better approach.