CODEWITHSUNDEEP
excelexcel-formula
blahblahblah
blahblahblah

Reputation: 2447

Excel Array Function to Sum a number of COUNTIF statements

I'm having difficulty phrasing this intelligently, but I'll try the long way -- I need a function that assigns a 1 or 0 depending on whether the row has an "x" in one of four previous columns and then sums all those assigned values.

In other words, imagine the following formula is in E2 and autofills down for any given number of rows. 

=IF(COUNTIF(A2:D2, "x")>=1, 1, 0))

I want to write an array function in F2 that would do the equivalent of summing all the values in column E (but don't reference column E).

Sum of ...

=IF(COUNTIF(A2:D2, "x")>=1, 1, 0))
=IF(COUNTIF(A3:D3, "x")>=1, 1, 0))
=IF(COUNTIF(A4:D4, "x")>=1, 1, 0))
=IF(COUNTIF(A5:D5, "x")>=1, 1, 0))
...for all of column A through D

Sample Data:

A         B         C         D 
20-Sep    20-Sep    21-Sep    21-Sep            
x         x 
          x         x
          x 
x           
                              x 
          x     
x           
x                   x   
x

The formula should return the value 9. There are 9 rows that have an "x" between the columns A and D.

Upvotes: 1

Views: 338

Answers (1)

andy holaday
andy holaday

Reputation: 2302

Try this out:

=SUM(--(MMULT(--(A2:D5="x"),{1;1;1;1})>0))

Commit normally (array entry will neither help nor hurt). If your production range is wider than 4 columns expand the {1;1;1;1} array.

Also works:

=SUM(N(MMULT(N(A2:D5="x"),{1;1;1;1})>0))

Upvotes: 4

Related Questions