Excel formula question

Posted on 2013-12-06
Medium Priority
Last Modified: 2013-12-12
Hi all,

quick question.

I have a spreadsheet showing work quoted. I have a column for the cost and a column where the purchase order number is entered.

without going in VBA is it possible to have a cell which totals the cost column only where there is a purchase order number entered in another cell?


Description  Cost             PO
Work 1          £10.00         C-102933
Work 2          £20.00        
Work 3          £10.00         C-102383
Work 4          £40.00         C-104452
Work 5          £50.00

Order Received £60.00
Outstanding      £70.00
Total                   £130.00
Question by:flynny
  • 2
  • 2
  • 2
  • +3
LVL 15

Accepted Solution

DonConsolio earned 400 total points
ID: 39700695
Create a new column containing an "IF()" formula

i.e. insert value 0 if column PO = "" , insert value of column Cost otherwise

and sum up that column.
LVL 54

Assisted Solution

Rgonzo1971 earned 400 total points
ID: 39700712
I suggest you use SUMIF for this purpose:


see example

LVL 35

Assisted Solution

by:Rob Henson
Rob Henson earned 400 total points
ID: 39700715
You can use SUMIF function.

=SUMIF(PO Column, "<>",Value Column)

Rob H
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 400 total points
ID: 39700718
If your numbers are in B2:B6 then use this formula

LVL 19

Assisted Solution

regmigrant earned 400 total points
ID: 39700722
or use 'Sumif" -

Order received: =SUMIF($D$2:$D$6,"> ",$C$2:$C$6)
Outstanding: =SUMIF($D$2:$D$6,"",$C$2:$C$6)

example attached
LVL 35

Expert Comment

by:Rob Henson
ID: 39700733
Does 4 people with effectively the same answer make it the right thing to do?

I wonder if BarryHoudini is going to chirp in with a SUMPRODUCT option or somebody else with a Pivot Table??

Happy Friday to one and all!

Rob H
LVL 19

Expert Comment

ID: 39700748
its the Christmas season - get back to work all of you

LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39700775
We all have nothing else to do. 4 same answers within less than 4 minutes.
LVL 50

Expert Comment

by:barry houdini
ID: 39701329
SUMIF, SUMPRODUCT, are you mad!?

You can use SUMIFS


or an "array formula"


confirmed with CTRL+SHIFT+ENTER

regards, barry

[definitely not for points]

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question