Sum and Counting Function

New Contributor


I have been trying to count and sum a row of "x" in a matrix structure, based on text in another line.


in cell D10, I would like to show the sum of "x" across the row AO10:ZZ10 only if there is corresponding text in the cell AO5 

this should show D10 being 2


and again in cell E10, sum up the "x" in row AO10:ZZ10 only if there is a corresponding text in cell AO4

This should show E10 being 1


any help would be gratefully appreciated.





3 Replies

@Alastair674 Created a mock-up worksheet based on your screen shot. See if it works for you. Am using SUMPRODUCT to count the number of x's on row 10, if a row above it is NOT blank. In D10 it looks for text in row 5, in E10 it looks for text in row 4. Assuming you want a similar calculation in F10 and G10, you can change the formula so that the last part of it points to rows 3 and 2 respectively. 


Thanks so much  @Riny_van_Eekelen , formula worked perfectly.

I was able to use that on several other aspects of the matrix.



Thanks again.

@Alastair674 Glad you could get it to work!