CountIf

[Roses8]

hi, Im trying to get a formula which:

Counts IF column A1 and in row C1 match against 'Error details sheet from I14:J177'

=COUNTIF('Error Details'!I14:J1715,A1:C2)

I can't seem to ge the desired result...

Error details sheet

Column I column J

AB1234 CITI
AB1235 JPM
AB1234 CITI

Error Talley Sheet

A1 = AB1234
C1 = CITI

the result should = 2

Thanks.

 

[NBVC]

I think I do.

When I first tested it on the small sample the OP gave, It gave the desired result, but then when I looked into why she was not getting the same result, I noticed that the formula only matched the first reference, ie AB1234. Obviously, that's wrong, but I didn't have enough variety in my sample to get the correct result.

I was going to suggest the sumproduct at first, but I thought the original formula was working after using the CSE combo. That's why I ended up suggesting the Sumproduct formula.

I guess I learned too. Those array formulas are confusing sometimes.

 

[Aladin Akyurek]

I think I do.

I don't think you do.

When I first tested it on the small sample the OP gave, It gave the desired result, but then when I looked into why she was not getting the same result, I noticed that the formula only matched the first reference, ie AB1234. Obviously, that's wrong, but I didn't have enough variety in my sample to get the correct result.

...

No. What it does is to create an array of occurrence frequencies involving all of the criteria something like:

{2,3,1}

Entered in a single cell, the formula will only display the topleft frequency involving the first criterion in the array object it creates.

 

[NBVC]

Like I said, I learned too.