• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

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.
0
sscastor115
Asked:
sscastor115
  • 4
  • 2
1 Solution
 
bclongacreCommented:
have you tried using =sumif as opposed to =countif

0
 
bclongacreCommented:
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
 
sscastor115Author Commented:
I did try =sumif, however I received a too many arguments error and thought it was due to have a nonconsecutive range.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bclongacreCommented:
=SUMIF(range of cells that = p, "P", range of cells to sum)
0
 
Rory ArchibaldCommented:
Try:
=SUMIF(D8:P8,"P",C8:O8)
0
 
bclongacreCommented:
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
 
sscastor115Author Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now