Using COUNTIF or COUNTIFS with LEFT Function

[GMak]

How to use COUNTIF or COUNTIFS with the LEFT function?

Example:
............COL B .......COL J
Row1...House #1.....Acme
Row2...House #1.....Acme
Row3...House #3.....Acme
Row4...House #5.....Acme
Row5...(blank).........Acme
Row6...(blank).........Acme
Row7...Joe..............Acme
Row8...House #7.....Acme

Count if B1:B8=“House” and J1:J8=“Acme”...The answer is 5. The actual table is 400 rows long, with multiple columns and combinations, but the above simplified version is the essence of the problem.

The LEFT function is necessary because (COUNTIF(B1:B8=“House” does not work because there is no B1:B8=“House”, as each term of “house” has a number, which CountIf will not count. But a LEFT(J1,5)="House" will work, because it eliminates anything after the word "House" if it appears.

Further, COUNTIF J1:J8= “Acme” will not work because it will count all instances of “Acme”, which is not correct.


I’ve tried:
=SUMPRODUCT((LEFT(B1:B8,5)=“House"),(J1:J8=“Acme”),B1:B8)
and
=SUM(IF(LEFT(B1:B8,5))="House",J1:J8,0))
and
= COUNTIFS(J1:J8,”Acme”,(IF(LEFT(B1,8)=“House",1,0))

...and a variety of other variations. I just can’t seem to fit “LEFT” into the SUM or COUNT functions.

Importantly, these are words that are being counted, not numbers. If the correct combination (“House” + “Acme”) appears, that’s “1”, so it is just straight addition.

Any help would be greatly appreciated. This is for a church project, and if I can get this formula going, it will really help manage some volunteer projects.

Thanks!

 

[John Jairo V]

Hi!

You can use:
=COUNTIFS(B1:B8,"House*",J1:J8,"Acme")

Blessings!

 

[GMak]

"That's a bingo!"* Gosh! An asterisk??? THANKS! I'll have to look into the use of an asterisk as I am not familiar with it in Excel and never used it in any of my formulas or macros. I understand it from the old DOS "wildcard" usage, but would have assumed it meant "'House' and anything after house..." as in the old DOS usage. In this case it appears, rather, to be a limiter. Hmmm! Learned something new. Thanks so much! (I put it into my workbook and works great!)

Thanks, John!


*from Inglorius Basterds

 

[Hui]

I understand it from the old DOS "wildcard" usage, but would have assumed it meant "'House' and anything after house..." as in the old DOS usage.

That is exactly what it means

So John's solution =COUNTIFS(B1:B8,"House*",J1:J8,"Acme")
reads as
Count the number of entries where Column B1:B8 =House with anything after house and column J1:J8 = Acme

 

[bosco_yip]

How to use COUNTIF or COUNTIFS with the LEFT function?..........
I’ve tried:
=SUMPRODUCT((LEFT(B1:B8,5)=“House"),(J1:J8=“Acme”),B1:B8)
and
=SUM(IF(LEFT(B1:B8,5))="House",J1:J8,0))
and
= COUNTIFS(J1:J8,”Acme”,(IF(LEFT(B1,8)=“House",1,0))
..........

Hi,

Try.....

=SUMPRODUCT((LEFT(B1:B8,5)="House")*(J1:J8="Acme"))

Or,

=SUMPRODUCT(ISNUMBER(FIND("House",B1:B8))*(J1:J8="Acme"))

Or,

this array formula, (confirmed enter with SHIFT+CTRL+ENTER)

=SUM(IF(LEFT(B1:B8,5)="House",IF(J1:J8="Acme",1,0)))

Regards
Bosco

 

[GMak]

I understand it from the old DOS "wildcard" usage, but would have assumed it meant "'House' and anything after house..." as in the old DOS usage.

That is exactly what it means

So John's solution =COUNTIFS(B1:B8,"House*",J1:J8,"Acme")
reads as
Count the number of entries where Column B1:B8 =House with anything after house and column J1:J8 = Acme

But then I guess don't understand... originally, I was using "LEFT" to try truncate anything else (different) after "House" as it didn't matter if it was "House '1', '2', '3', etc., only if it was "house". But if the "*" (asterisk/wildcard) means "and anything after it" then "House #1" would be different from "House #2", from "House #3"... invalidating the formula, no?

Unless, the "*" wildcard means "and ignore anything after it..." which would then have the same effect as "LEFT 5" to truncate the evaluation to just the letters "House"...?

This "wildcard" opens up amazing new possibilities, that's why I'm asking, to try to make sure I understand it.
Thanks everyone for your input! Much appreciated.

 

[Hui]

Countif/s, Sumif/s and Averageif/s have limited functionality of what they can/can't include as criteria

So you cannot use Left(Range,5), "House"
But you can use Wildcards as in Range, "House*"

Which mean House with anything after it including nothing after it

Now other functions like Sumproduct allow other functions to be embedded within them and so the use of Left(Range,5)="House" is allowed

When you start typing a formula you will see that Excel pops up some help
Sumproduct accepts an Array or Range as an input
Sumifs etc accepts a Sum_Range and a Criteria_Range
there is limited functionality available for the criteria except that wild cards are allowable