sscastor115

asked on

# Excel 2003 count cell value based on adjoining cell value

I'm trying to automate the totals counting on a paysheet. I've used the below but it only returns values if the condition exists in all cells.

=IF(AND(D8,F8,H8,J8,L8,N8,P8 ="P"),SUM(C8,E8,G8,I8,K8,M8,O8),"")

However I would like to count only those that have the value of P and ignore the others. So if D8 and L8 had P it would count, but if F8 had H it would be ignored. Right now if I change the F8 the total does not change.

I have also tried a =countif(C8:O8;"P") however it counts the occurrences of P and not the value within.

=IF(AND(D8,F8,H8,J8,L8,N8,

However I would like to count only those that have the value of P and ignore the others. So if D8 and L8 had P it would count, but if F8 had H it would be ignored. Right now if I change the F8 the total does not change.

I have also tried a =countif(C8:O8;"P") however it counts the occurrences of P and not the value within.

have you tried using =sumif as opposed to =countif

of another possible option, is adding a hidden column, that has the formula =if(d8="P",d8,"") fill the formula down, then run your sum of the hidden column.

ASKER

I did try =sumif, however I received a too many arguments error and thought it was due to have a nonconsecutive range.

=SUMIF(range of cells that = p, "P", range of cells to sum)

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

I see what you are saying, you are alternating data in one column with your trigger in the next column, correct?

If that is the case, the I would adovcate the addition of a hidden row, either above or below that would use a if statement to populate your condition directly above or below your value.

For example if you have a Value in D8 and a condition in E8, in cell D9 place the formula =if(E8="P",E8,""), then fill right if applicable

Then for your sum statement, use =sumif(D9:O9,"P"P,D8:O8)

If that is the case, the I would adovcate the addition of a hidden row, either above or below that would use a if statement to populate your condition directly above or below your value.

For example if you have a Value in D8 and a condition in E8, in cell D9 place the formula =if(E8="P",E8,""), then fill right if applicable

Then for your sum statement, use =sumif(D9:O9,"P"P,D8:O8)

ASKER

Thats amazing the difference seems to be in what I tried and you gave me is that I included the C8 as part of the range of cells. Thanks very much.