Excel formula question

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?

i.e.

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
flynnyAsked:
Who is Participating?
 
DonConsolioConnect With a Mentor Commented:
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.
0
 
Rgonzo1971Connect With a Mentor Commented:
I suggest you use SUMIF for this purpose:

=SUMIF(D2:D6,"<>",C2:C6)

see example

Regards
Sumif1.xlsx
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
You can use SUMIF function.

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

Thanks
Rob H
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
If your numbers are in B2:B6 then use this formula

=SUMIF($C$2:$C$6,"<>",$B$2:$B$6)
0
 
regmigrantConnect With a Mentor Commented:
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
Book2.xlsx
0
 
Rob HensonFinance AnalystCommented:
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!

Thanks
Rob H
0
 
regmigrantCommented:
its the Christmas season - get back to work all of you

reg
0
 
Saqib Husain, SyedEngineerCommented:
We all have nothing else to do. 4 same answers within less than 4 minutes.
0
 
barry houdiniCommented:
SUMIF, SUMPRODUCT, are you mad!?

You can use SUMIFS

=SUMIFS(B2:B6,C2:C6,"<>")

or an "array formula"

=SUM(IF(C2:C6<>"",B2:B6))

confirmed with CTRL+SHIFT+ENTER

regards, barry

[definitely not for points]
0
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.

All Courses

From novice to tech pro — start learning today.