Sum a Column when a row says a specific text

Question

On the OMS IV tab I need H22 to add the number of weeks in column D if j15:ab15 says non-clinical. Column D turns light yellow when the corresponding J15 etc shows non-clinical, but now I need to add up all the yellow colors to sum in h22

https://docs.google.com/spreadsheets/d/115i_HCIkJqJegGb2o0AjEyYy6ldePcAJewz-QK1Lk2A/edit?usp=sharing

It is only adding one of them. I have tried sumif, sumifs, sumproduct, array formula in a lot of different ways

Answer 1

You may try:

=sumif(TRANSPOSE($J$15:$AB$15),"Non-Clinical",$D$4:$D$22 

The SUMIF function only works correctly when the sum_range has the same dimensions as the criteria_range. Therefore, I used TRANSPOSE to convert the sum_range.

References:

• SUMIF
• TRANSPOSE