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]

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)

Why wouldn't it be 2?

BU1 = 14, BV1 = 14, and K1 = s

 

[T. Valko]

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)

Try this...

=SUMPRODUCT(--(K1:K7="s"),--(MMULT(--(BT1:BV7=14),{1;1;1})>0))

You should avoid using entire columns as range references when using the SUMPRODUCT function.

 

[jimblimm]

answer should be 1
your formula counts the 14 twice even though the "s" doesnt line up with the 14

 

[jimblimm]

Im not using entire columns, i just typed that in as an example.....that formula works though, just manually checking right now to make sure, thanks again

 

[jimblimm]

it wouldnt be 2 because it doesnt matter if the 14 shows up twice in same row, i am only counting it once,

now if that string showed up in say

BU2 = 14, BV2 = 14, and K2 = s

also, then it would be 2

 

[T. Valko]

Im not using entire columns, i just typed that in as an example.....that formula works though, just manually checking right now to make sure, thanks again

Good deal!

Just for clarification...

The array constant: {1;1;1}

Refers to the number of columns in the range BT:BV.

 

[jimblimm]

Good deal!

Just for clarification...

The array constant: {1;1;1}

Refers to the number of columns in the range BT:BV.

i always wondered what that meant,,, i was looking at the formula while i was typing mine and it was like it was looking at every cell individually..thanks a mil...

 

[T. Valko]

i always wondered what that meant,,, i was looking at the formula while i was typing mine and it was like it was looking at every cell individually..thanks a mil...

You're welcome!

 

[jimblimm]

You're welcome!

quik question ..

is there a way to use the same formula with lookup or index to show last occurring, i have a numbered column in a:A

 

[T. Valko]

quik question ..

is there a way to use the same formula with lookup or index to show last occurring, i have a numbered column in a:A

Try this array formula**:

=INDEX(A:A,MAX((K1:K7="s")*(BT1:BV7=14)*ROW(BT1:BV7)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.