Sumproduct?

[shemayisroel]

Hi,


Can't seem to figure this out, formula is

=SUMPRODUCT(--(ISNUMBER(MATCH(M4:M48,{"HIT","MISS"},0))))

In M4:M48 are results from formulas either returning HIT, MISS or blank.

The SUMPRODUCT formula returns 0 but there is actually HIT or MISS in the range. When I manully type over the rannge with either HIT or MISS the SUMPRODOUCT formula returns the expected result

Checked, calculation, format of cell etc

 

[jbeaucaire]

Check Tools > Options > Calculation and make sure it's set to automatic.

 

[Haseeb Avarakkan]

Hello,

May be M4:M48 have extra spaces, like 'Hit ', 'Miss '. if you just have 2 criterias try this,

=SUM(COUNTIF(M4:M48,{"*Hit*","*Miss*"}))

 

[shemayisroel]

Check Tools > Options > Calculation and make sure it's set to automatic.

Did that - no good

 

[shemayisroel]

Hello,

May be M4:M48 have extra spaces, like 'Hit ', 'Miss '. if you just have 2 criterias try this,

=SUM(COUNTIF(M4:M48,{"*Hit*","*Miss*"}))

Wont work, the SUMPRODUCT is a snippet but this is the part that is failing...

 

[shemayisroel]

Figured it out! Well sort of...

I was using the TODAY() function in the range to return a result (HIT/MISS), for some reason this was killing the SUMPRODUCT formula. Don't need to know why I just used a work around.