2 array countifs

[jimblimm]

is there any way to make this formula work

=COUNTIFS('MAIN DATA'!$BT$20:$BV$4000,A11,'MAIN DATA'!$K$20:$K$4000,"b11"))


i keep getting a value error

if i split the countifs it works.

COUNTIFS('MAIN DATA'!$BT$20:$BV$4000,A11

COUNTIFS(MAIN DATA'!$K$20:$K$4000,"b11"

 

[c_m_s_jr]

Maybe:

=COUNTIFS('MAIN DATA'!$BT$20:$BT$4000,A11,'MAIN DATA'!$BU$20:$BU$4000,A11,'MAIN DATA'!$BV$20:$BV$4000,A11,'MAIN DATA'!$K$20:$K$4000,"b11"))?

Though I don't think that will give you the results you want:

So maybe really:

=SUMPRODUCT(--(('MAIN DATA'!$BT$20:$BT$4000=A11)+('MAIN DATA'!$BU$20:$BU$4000=A11)+('MAIN DATA'!$BV$20:$BV$4000=A11)),--('MAIN DATA'!$K$20:$K$4000,"b11"))

 

[T. Valko]

is there any way to make this formula work

=COUNTIFS('MAIN DATA'!$BT$20:$BV$4000,A11,'MAIN DATA'!$K$20:$K$4000,"b11"))


i keep getting a value error

if i split the countifs it works.

COUNTIFS('MAIN DATA'!$BT$20:$BV$4000,A11

COUNTIFS(MAIN DATA'!$K$20:$K$4000,"b11"

Is "b11" a cell reference or is it a literal string?

COUNTIFS will only work on 1 dimensional arrays.

You can use SUMPRODUCT.

=SUMPRODUCT(('MAIN DATA'!$BT$20:$BV$4000=A11)*('MAIN DATA'!$K$20:$K$4000=B11))

 

[jimblimm]

nope, because if say for instance im looking for 21 in array bt:bv, your formula will return negative if all ranges dont meet the criteria, bt:bt and bu:bu

for instance bt could have 21

and bu does not, it will never count

i need it to look for if bt:bv= 21 it should count it whether its in bt:bt bu:bu bv:bv /hence bt:bv

 

[c_m_s_jr]

nope, because if say for instance im looking for 21 in array bt:bv, your formula will return negative if all ranges dont meet the criteria, bt:bt and bu:bu

for instance bt could have 21

and bu does not, it will never count

i need it to look for if bt:bv= 21 it should count it whether its in bt:bt bu:bu bv:bv /hence bt:bv

See my adjusted post in #2 using SUMPRODUCT. The "+" acts like an OR statement in SUMPRODUCT (I believe I learned that from T.Valko on this board).

 

[jimblimm]

example:
k:k
s
d
r
t
s
s
d


bt:bt
11
12
14
16
15

bu:bu

14
14
15
12
22

bv:bv
14
18
98
78
54

 

[jimblimm]

it should count once if the arrays all match criteria

countifs(bt:bv=12,k:k=s

 

[c_m_s_jr]

example:
k:k
s
d
r
t
s
s
d


bt:bt
11
12
14
16
15

bu:bu

14
14
15
12
22

bv:bv
14
18
98
78
54

=SUMPRODUCT(--(('MAIN DATA'!$BT$20:$BT$26=A11)+('MAIN DATA'!$BU$20:$BU$26=A11)+('MAIN DATA'!$BV$20:$BV$26=A11)),--('MAIN DATA'!$K$20:$K$26=B11))

Where I assumed "s" for B11 and 14 for A11. Formula result is 2

 

[T. Valko]

example:
k:k
s
d
r
t
s
s
d


bt:bt
11
12
14
16
15

bu:bu

14
14
15
12
22

bv:bv
14
18
98
78
54

Here's the data you posted:

Book1
*	K	BT	BU	BV
1	s	11	14	14
2	d	12	14	18
3	r	14	15	98
4	t	16	12	78
5	s	15	22	54
6	s	_	_	_
7	d	_	_	_
Sheet1

What are you wanting to count and what result do you expect?

 

[jimblimm]

countifs(K:k=s, and bt:bv=14 going by the chart my answer would be 1.

even though the 14 appeared in bu:bu and bv:bv the formula would kinda be like an (or) for the BT:BV

like countifs(k:k, (bt:bt=14,or bu:bu=14 or bv:bv=14)