Sum and Counting Function

New Contributor

Hi, 

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.

 

Alastair674_1-1612399870892.png

 

 

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. 

@Riny_van_Eekelen 

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!