Feb 03 2021 05:19 PM
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.
Feb 03 2021 10:23 PM
@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.
Feb 04 2021 12:21 AM
Thanks so much @Riny_van_Eekelen , formula worked perfectly.
I was able to use that on several other aspects of the matrix.
Thanks again.