Monday-itis and Multiple Criteria - Array Formula? 1

[kjv1611]

Alright, this should be so simple, yet I apparently can't get my brain to cooperate.

First some sample data. This is looking at transaction history for a group of accounts: (and it's in a table, I'll call Accounts)
[TT]
AccountID TransAmount TransType OtherFields..
11111 100 TypeA
11111 300 TypeA
11111 50.55 TypeB
11111 557.12 TypeA
11111 419.50 TypeC
11111 10349.99 TypeB
22222 12 TypeA
22222 422 TypeA
22222 50.55 TypeB
22222 627.92 TypeA
22222 439.50 TypeC
22222 49.99 TypeB
33333 18 TypeA
33333 427 TypeA
33333 51.55 TypeB
33333 115.13 TypeA
33333 .50 TypeC
33333 49.99 TypeB
[/TT]

So I'm trying to use an array formula, which I KNOW is not correct, but seem to get my mind to think about what SHOULD work.

Here's my array formula:

{=SUM(([TransType]="TypeC")*([AccountID]=[@AccountID]))}

[/CODE]

So why it's not working is that I'm not looking for every instance where AccountID = THIS AccountID AND it is of TypeC, but what I really want to know is: Which Records are related to an Account that has a TransType = "TypeC".

Can anyone shed any light on my Monday stupor?

p.s. Please forgive my columns being squished together. Apparently TT doesn't seem to help anymore, nor does manually spacing.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

hi Steve,

Check out faq68-7708.

I recently wrote it.

For your posted example, this would only work for TypeC, as it is the only TransType that has a single row per AccountID.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[kjv1611]

Hmm... well, that stinks, because it is POSSIBLE that there are more than one row for the particular combination.

But an interesting mention there.

So should I just handle this via query, then?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

I guess I was trying to use the wrong tool for the job. I can just query what I need another way. But it seemed so simple when I was trying it at first. Thanks for the attempt, though.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Actually... thinking about it.. I suppose this COULD work, still, in that I only need to see which accounts meet the condition. I don't really care if there is more than one instance, I just need to know that Account 11111 has a TypeC transaction for what I'm looking for. And then I want to see all transactions for that account.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

[

I just need to know that Account 11111 has a TypeC

Use COUNTIFS()

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[kjv1611]

Yeah, I was going to, but thought I couldn't do multiple critieria for count-if. Maybe I'm forgetting. Let me take a look.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Yep, from the quick description I just read, COUNTIFS handles multiple critieria - I was just thinking there was COUNTIF.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Didn't suggest SUMIF()!!!

Rather SUMIF[highlight #FCE94F]S[/highlight]()

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[kjv1611]

Yep

And guess what. My initial formula DID work - I just goofed and typed the TransType backwkards!

So now I have 2 formulas that worked! Array Formula AND COUNTIFS!

Thanks again

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

I meant COUNTIFS() rather than SUMIFS()

Uhg!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}