Solved

Excel 2003 count cell value based on adjoining cell value

Posted on 2011-02-14
7
366 Views
Last Modified: 2012-05-11
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
Comment
Question by:sscastor115
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 7

Expert Comment

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

0
 
LVL 7

Expert Comment

by:bclongacre
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

by:sscastor115
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 7

Expert Comment

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

Accepted Solution

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

Expert Comment

by:bclongacre
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

by:sscastor115
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question