SUM rows based on criteria

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?
 
P

Peo Sjoblom

You could use this instead

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

put the criteria in H1
 
A

Annabelle

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.
 
P

Peo Sjoblom

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..
 
D

Debra Dalgleish

At least you're consistent <g>

"it will retrun anything that starts with "ABC""
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find last column matching criteria 2
create new table from Multiple Criteria in multiple columns 3
Conditional Multiply and Sum 2
Sum values in columns based on values in named range 10
SUMIF & COUNTIF based on dates 10
function returning SUM of 2 adjacent cols in 2 separate cells 7
Counting number of rows based on mutiple criteria 2
sum cells based on contents of adjacent cell (validated list)? 3

Top