Adding only certain cells in a column

[innatedoc]

For the attached workbook, does a formula exist that would allow me to only count cells that are under particular columns in each row? For instance, at the end of a row I would like to simply count the number of "P" cells that have data entered into them. Thank you!

[oeldere]

It is possible with sumif (see the attached file).

The result is in the green cells.

[innatedoc]

Awesome - thanks!

[oeldere]

Glad I could help you.

Will you mark the question as solverd?

[innatedoc]

I thought I did - sorry. I will try again. Isn't there a way to give you a star or something for helping too?

[innatedoc]

You know, upon further reflection and now that I'm home from work to play around with the workbook, the formula doesn't quite do what I was hoping to learn to do. It appears that the formula totals the numbers in the cells in the "P" columns. I am wondering if there is a way to count a "P" cell with any entry as a "1" and not count any other cells. I don't want to add the numbers in the "P" cells, I'd like to count the number of "P" cells that have data entered in them in a particular row. Is there a way to do that?

[FDibbins]

change the sumif() formula to sumifS()

=SUMIFS(A4:X4,A3:X3,Z3,A4:X4,1)

[innatedoc]

thanks! i'll try that out . . .

[innatedoc]

Hmmmmm . . . there exists the distinct possibility that i am doing this wrong, but when i put that formula in it comes up with a "1" no matter how many "P" cells in the row contain data (unless no "P" cells contain data, in which case it comes up with a "0")

[FDibbins]

sorry, dont know why i was thinking "add". if you want to count the P's with 1 in them, try this...

=SUMPRODUCT((A3:X6=1)*(A3:X3=Z3))

if you want a separate total for each row, use this...
=COUNTIFS(A3:X3,Z3,A4:X4,1)

[innatedoc]

I think we're getting closer! I'd like to count a "P" with ANY data in it as a 1, and I need to be able to do it on each row.

if a "P" has "$4.00" in it I'd like to count it as a 1. If it has "$0.50" in it, I'd like to count it as a one. No matter what the data is I'd like to count it as a 1, unless there is no data and then I'd like not to count it. Kind of like a binary thing - it's either a 1 or a 0 (yes or no) no matter what data the cell contains. Is that even possible?

[FDibbins]

ok, i thought you only wanted to coiunt IF it had 1 in. change to this, for each row...
=COUNTIFS($A$3:$X$3,$Z$3,A4:X4,">0")

[innatedoc]

Awesome! that worked in my test file. I'm going to attempt to adapt it to my actual workbook and see if it works. Thanks!

[FDibbins]

glad to help, and thanks for the rep