Link to home
Start Free TrialLog in
Avatar of sscastor115
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.
Avatar of bclongacre
bclongacre
Flag of United States of America image

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.
Avatar of sscastor115
sscastor115

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
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)
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.