I have an Excel workbook in which I want to look at the contents of column A, and if they meet certain criteria (A,B or C) sum the contents of the corresponding cell in column I, unless that value is outwith a certain range (less than zero or greater than 1000). I have tried using sumif in various ways without much success. I am sure this is simple, but I would appreciate some help!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Conditional Counting Across Columns
- Thread starter stc
- Start date
- Thread starter
- #3
- Thread starter
- #4
Having tried the suggestions on the FAQ, I'm still not quite managing to do what I need to. The problem seems to be that the values in column I are figures and I want to be able to sum the values of all the cells in that column if they meet the criteria in column A, unless they are negative.
...or greater than 1000 - yes I know - you've already stated that - just requires conditions
=SUMPRODUCT((A1:A1000="Criteria")*(I1:I1000>0)*(I1:I1000<1000)*(I1:I1000))
would be my 1st stab at it
assumes data in A1:A1000 and I1:I1000 - amend as appropriate
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
=SUMPRODUCT((A1:A1000="Criteria")*(I1:I1000>0)*(I1:I1000<1000)*(I1:I1000))
would be my 1st stab at it
assumes data in A1:A1000 and I1:I1000 - amend as appropriate
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
- Thread starter
- #6
- Status
Similar threads
- Locked
- Question