SUM rows based on criteria

  • Thread starter Thread starter Annabelle
  • Start date Start date
A

Annabelle

Description: At the bottom of my worksheet, I need to sum
rows in a column based on the content of another column; I
also want the ability to add additional rows without
manually updating the sum formula. Although the details
below show only 6 rows, the data could grow to 100's.

Details:

Row Col F Col G (number of hours)
6 Projected 120
7 Current 123
8 Projected 41
9 Current 41
10 Projected 77
11 Current 98
____________________
(SUM)
14 - 238 (all Col G rows where Col F
contains "Projected")
15 - 262 (all Col G rows where Col F
contains "Current")

I believe an IF statement is appropriate, but having
trouble with its usage with these conditions. Any
suggestions?
 
You could use this instead

=SUMIF(F2:F100,"*"&H1&"*",G2:G100)

put the criteria in H1
 
I haven't seen the * character used for anything except
multiplication before. What does it refer to in these two
formulas you've given me? Again, thank you for your help.
 
It's called wild card where an asterisk represent one or more letters in a
word. Like if you search in windows
and you can use *.xls to search for any excel file, ? represent one
character so you can use

"?word" wher it would retrun anything like aword 1word bword etc..

So in this formula it means any text before and after just as long as the
string "Projected" is there
If you would look for anything that starts with "ABC" you can use

"ABC*"

which tell you that it will retrun anything that starts with "ABC"
regardless what comes after..
 
Consider using the Pivot Table function (under Data
menu). Follow the wizard - when you construct the layout
of the table, pull col F to "Row" and pull Col G
to "Data". Double click on this data button and set it to
summarize by "sum". I find the pivot table very useful to
generate summaries for a large table.
 
Back
Top