If Cell Contains "PRODUCT NAME" then count all the cells that contain the letter "a"

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

while you are mocking up a workbook, you might want to address the following:
1) is "teflon" alone in a cell in column D or can it be part of a longer description like red teflon pan?
2) same question for the letter A, does it appear alone in a cell and that is what you want to count in the range of from columns J through U?
3) lastly, most often the way sumifs and countifs work is by counting across the SAME row, so that if D17 has teflon and K17 has A you would count it but what would be the criteria to count a row P200 that contains an A when your range for counting "teflon" ends at row 100? Usually the ranges have to be the same or the formula will not work.

Remove the trailing space from H1, then in E2 copied across and down:

=SUMPRODUCT(($D16:$D21=D2)*($J$16:$U$21=MID(RIGHT(E$1,3),2,1)))

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

That doesn't work. The $ are missing and it doesn't count the cs.

So change the headers to ACTIVE ( a ) ... with a space either side of the code letter... to prevent Excel changing (c) to the copyright symbol.

Delete the training space in H1, as mentioned.

Delete the value in D6, TEFLON-PTFE. It's aready there in row 2.

Then use this, with the $ signs correctly assigned:

=SUMPRODUCT(($D$16:$D$21=$D2)*($J$16:$U$21=MID(RIGHT(E$1,4),2,1)))

It gives a total of 16 (the correct answer). If you want to remove the zeros, which can be a bit distracting, modify to:

=IFERROR(1/(1/SUMPRODUCT(($D$16:$D$21=$D2)*($J$16:$U$21=MID(RIGHT(E$1,4),2,1)))),"")

That doesn't work. The $ are missing and it doesn't count the cs.

Good spot.

Works fine without adding the spaces i,e "(c)" but with correct absolute addressing.

Please try,

change D2 to POLYETHYLENE

Formula in E2
=SUM(IF(($D$14:$D$86=$D2),--($J$14:$U$86=LOWER(LEFT(E$1,1)))))

Follow with Ctrl-Shift-Enter for array formula.

Regards.