Counting values in an adjoining column of non-null data

[annewtsn]

I am having some trouble coming up with the right formula for a project at work.

I have a spreadsheet that is counting individual volunteers and groups and the number of hours in which they serve. Column A is the number of persons serving, B and C are their names and D through I count the number of hours they worked at the particular jobs.

I am trying to total the number of people who perform a certain job and am struggling with the right syntax. Basically what I want to do is if the number in column D is not null, I want to total the number in column A. To count the number of persons, I tried this formula, =IF(COUNTA(D2:D43),SUM(A2:A43)) However, that totals all the persons in column A, not just the ones with values in column D.



Any help you could provide would be appreciated.

[oeldere]

You get better help if you post an Excel-example of your workbook, without confidential information.

Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix.

Use BEFORE/AFTER sheets if that helps make it clearer.

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[annewtsn]

The yellow highlighted cells are the ones in questionexample.xlsx

[annewtsn]

Title changed. Sorry.

[NBVC]

Possibly:

=SUMIF(D2:D43,"<>",A2:A43)

[annewtsn]

Yes - that worked! Thank you!

[oeldere]

See the example.

Notice I changed your sheet.

The result is in R2 - S8 ( the red cells).

When you change the input this way, you have a table.

In that case it is mutch easier to count / reproduce data.

I worked with defined names:

Excel 2007 => formula => defined names.

I worked with datavalidation

Excel 2007 => data => datavalidation => list => job

Do you have questions, on this item, just ask.

Please reply your opinion.