COUNTIFS and SUMIFs statement - contains specific text OR specific text - without an array?

[banneduser123]

hi, two questions: wondering what the most efficient way to write these would be - without just combining multiple countif functions on top of one another to do this without using an array?

1: count the number of times any of the following appears in column R - ABS, BND, or FUND; while exlcuding any time CMO appears.


2. give the sum of any time ABS, BND, or FUND appears in column R; while excluding any time CMO appears.


if it's not possible to do without an array...how would you write the array?

thanks for any help

 

[pgc01]

Hi

Not clear

If you are only counting ABS, BND, or FUND what do you mean by "while exlcuding any time CMO appears"?

CMO is not being counted.

 

[banneduser123]

hi, thanks for the help...

I was just looking for the functionality and was going to tailor whatever help I got later....but there's a lot more criteria...I'm also counting any time a variation of "MBS" appears, by using *MBS*...the problem is then it also picks up "CMBS"....that's why I was just looking for the functionality...is that clear?

 

[pgc01]

Hi

Maybe something like:

=SUM(COUNTIFS(R:R,{"ABS","BND","*MBS*"},R:R,"<>CMBS"))

This will count ABS, BND, and strings including MBS, excluding CMBS

 

[banneduser123]

Thanks man.
You know what, I actually tried that while trying to figure it out on my own, but wasn’t sure if it was an array or not. I see that it isn’t now..

How would you do the sumifs function for my second question?

 

[pgc01]

I'd use a similar syntax using SumIfs(). Assuming you add the values in column T for the rows with match:

=SUM(SUMIFS(T:T,R:R,{"ABS","BND","*MBS*"},R:R,"<>CMBS"))

 

[banneduser123]

beautiful..thanks for the help

 

[pgc01]

You're welcome. Thanks for the feedback.