Complax Excel Formulas 2

[hamburg18w]

I need to build a formula that would like up specific names in Column A and return a corresponding time spend on the project from column G. Example:

Col A - List of Activities (I want to be able to include more than one specific activity in the formula, but the activities in the formula will remain the same)
Col B - Time spent on the project
Cel C25 - Total time spent on specific activities

Can someone help me, please.

Juan

 

[macropod]

Hi Juan,

From your problem description, I sounds like Excel's SUMIF function will do what you want. Have you tried that? If it's not suitable, I think you'll need to provide more information about your data structure etc.

Cheers

[MS MVP - Word]

 

[hamburg18w]

Thanks for your response, but it seems that I did not explain the problem well enough. Her is the second try:

Column A has a list of activities that can change from day to day. The same activities might fall into a different cell the next day. I need a formular that will look at a range of rows in Column A and find specific activities such as Vacation, Sick, Holiday, etc.

Column G has the corresponding time the activity took.

Cell C25 is the formula cell. I want the formula to come up with a total time spend (Column G) of specific activities (Column A). See the example below:

Column A Column G
-Report 1:30
-Sick 5:00
-John Doe 2:30
-General Time 0:45
-Etc.

Cell C25: The result of the time spent on "Reports" and "John Doe". Remember that reports can be on different rows tomorrow.

I hope this clarifies the issue, and thanks for trying to help.

Juan

 

[macropod]

Hi Juan,

It still seems like SUMIF is the tool for the job. For example, in C25 you could use:
=SUMIF(A:A,B25,G:G)
where B25 holds "Report" or "John Doe". This would give you the sum of all rows in Column G for which the string in B25 appears in Column A. Alternatively, you could use:
=SUMIF(A:A,"Report",G:G)
or
=SUMIF(A:A,"John Doe",G:G)
to achieve the same ends.

If you want to aggregate "Report" and "John Doe" and you don't want to use a reference cell, you could use a pair of SUMIFs coded as:
=SUMIF(A:A,"Report",G:G)+SUMIF(A:A,"John Doe",G:G)
or you could use them with reference cells.

If this isn't what you want, post back with more details.

Cheers

[MS MVP - Word]

 

[KenWright]

And if you want to aggregate more than one type of activity, simply add the SUMIFs, eg

=SUMIF(A:A,"Report",G:G)+SUMIF(A:A,"Sick",G:G)

or if you had a number of them and wanted less formula, you could also SUMPRODUCT, eg

=SUMPRODUCT((A2:A1000={"Report","Sick"})*B2:B1000)

but you can't use full column references with SUMPRODUCT, and if the numeric range contains a text header you should avoid having that in the range with the syntax I have given you, hence I started at row 2.

Failing that, a Pivot Table would also do what you want, though you would need to refresh it when the data changes.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Hi,

I would suggest that you rethink your design. The clue to this conculsion is that you have a column total in C25. Your design assumes that your data will NEVER exceed 24 rows.

Then you state, "...reports can be on different rows tomorrow."

Then what happens to yesterday's data? Does it get wiped out? Doesn't the accumulation of that data make it possible to determine what was going on in a particular week, month, quarter or year?

It seems to me that a vilid question to ask is, if this data is important enought to record, should it not be preserved to enhance it's usefulness?

By the way, the aggregation of data of varying rows, is best performed in a sheet OTHER THAN the table


Skip,

_[red][/red]