Solved

# Excel 2003 count cell value based on adjoining cell value

Posted on 2011-02-14
355 Views
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.
0
Question by:sscastor115
• 4
• 2

LVL 7

Expert Comment

ID: 34888925
have you tried using =sumif as opposed to =countif

0

LVL 7

Expert Comment

ID: 34888938
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.
0

Author Comment

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

LVL 7

Expert Comment

ID: 34889068
=SUMIF(range of cells that = p, "P", range of cells to sum)
0

LVL 85

Accepted Solution

Rory Archibald earned 500 total points
ID: 34889078
Try:
=SUMIF(D8:P8,"P",C8:O8)
0

LVL 7

Expert Comment

ID: 34889160
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)
0

Author Closing Comment

ID: 34889165
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.
0

## Featured Post

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.