can the SUMIF function look up a cell?

  • Thread starter Thread starter Clarence Crow
  • Start date Start date
C

Clarence Crow

Hello group

I'm a Consultant Estimator and when preparing Tenders for submission,
we're always faced with a multi-lined Pricing Schedule, obviously
compiled by an Accountant, plus a different multi-lined Scope of Works
Schedule, obviously compiled by a more logical Engineer.

To head this dilemma off at the pass, we have been Inserting an extra
column in the far left of the Worksheet and copying all the Pricing
Schedule Line Item Nos down to align with the appropriate Scope of
Works Line Item and using the SUMIF function to roll up the Price Line
Items.

This can be tedious when you have over 200 or so Price Line Items, as
we have to state the Criteria for each line, viz:
=SUMIF(range,"2.3.1",range).

It would be less tiring if the function could look up a cell for the
Criteria, viz: =SUMIF(range, "A2",range).

Can anyone suggest a solution or a work-around for this REALITY
problem?

Clarence

PS: please reply to this Group as my email doesn't work due to spam :)
 
Hi Clarence
you're nearly there. Try
=SUMIF(range_1,A2,range_2)

Frank

Clarence said:
Hello group

I'm a Consultant Estimator and when preparing Tenders for submission,
we're always faced with a multi-lined Pricing Schedule, obviously
compiled by an Accountant, plus a different multi-lined Scope of Works
Schedule, obviously compiled by a more logical Engineer.

To head this dilemma off at the pass, we have been Inserting an extra
column in the far left of the Worksheet and copying all the Pricing
Schedule Line Item Nos down to align with the appropriate Scope of
Works Line Item and using the SUMIF function to roll up the Price Line
Items.

This can be tedious when you have over 200 or so Price Line Items, as
we have to state the Criteria for each line, viz:
=SUMIF(range,"2.3.1",range).

It would be less tiring if the function could look up a cell for the
Criteria, viz: =SUMIF(range, "A2",range).

Can anyone suggest a solution or a work-around for this REALITY
problem?

Clarence

PS: please reply to this Group as my email doesn't work due to spam
Click to expand...
:)
 
Hi Clarence, Frank has given you the answer you need, but you might also want to
consider looking at Pivot tables for a neat way of analysing your data. I'm the
senior pricing analyst for Lockheed Martin here in the UK, and have similar
issues when analysing all the BOEs that come in from the various engineers etc.

Given that you have your SOW items in a single column, and the pricing data
alongside it, you can select that entire section of the spreadsheet, and
literally in about 60 seconds flat, create yourself a table that nets it all out
by line item, timeperiod (if you have it) etc. I would still create your
summary tables using the formulas, as the formulas will be completely live,
whereas with the Pivot table you would have to refresh it if the data changed
(although a couple of clicks is hardly a problem), but the Pivot table will
allow you to group data in ways that would take an eternity using formulas, and
it can often throw up some interseting number patterns that you might just not
have spotted in other ways. In case you haven't guessed, I am a complete
convert, and use them extensively in every bid I price. Don't know what I'd do
without them now.

One of the most useful ways I have found of using them, is to compare data from
different bid iterations:-

Assuming you have a set of data OLD and a set of data NEW in different sheets
for the same bid, I would insert a new column in front of each and put OLD right
the way down that column in the old data and NEW right the way down that column
in the New data. Then put a -1 in a cell, copy it and the paste special /
multiply across the numeric values in the old data, which will make them all
negative (bear with me now).

Combine both sets of data by copying one set under the other, create a Pivot
table and you can have NEW next to OLD. The Grand total columns will show you
any differences. Where there are 0s you have no difference (ie the +ve and
the -ve will cancel each other out), but anywhere in the total column you see a
value, there is a difference. That can all be done 'literally' in a matter of
minutes once you are comfortable with pivot tables. Debra Dalgleish has a
really nice intro if you've not touched them before.

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

There are just so many possibilities for analysis with these things in your line
of work, I can't recommend looking at them strongly enough, and we are always
here to help if you need a hand.
 
Hi Clarence
you're nearly there. Try
=SUMIF(range_1,A2,range_2)

Frank
<snip>
Click to expand...

Great! I tried that and it worked :)

What puzzles me that in the excerpt from the Excel HELP Screen below,
it doesn't mention that the Criteria can actually be a Cell Address?
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SUMIF(range,criteria,sum_range)

Range is the range of cells you want evaluated.

Criteria is the criteria in the form of a number, expression, or
text that defines which cells will be added. For example, criteria can
be expressed as 32, "32", ">32", "apples".

Sum_range are the actual cells to sum. The cells in sum_range are
summed only if their corresponding cells in range match the criteria.
If sum_range is omitted, the cells in range are summed."
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
We can't afford the time to learn and use Pivot Tables in out existing
setup, but may look at it further down the track, if it's possible
with the large No of columns we have in our existing Costing
Worksheet, which is just somewhere in the middle of a whole raft of
inter-linked Sheets in the overall Workbook.

Thanks, Clarence

PS: please reply to this Group as my email doesn't work due to spam.
 
Hi
Thanks for the feedback.
Maybe this kind of criteria usage seemed obvious for the writers of the
help file :-).
You can nearly always replace fixed criteria with an approbiate cell
reference.
 
Back
Top