SUMIF or SUMPRODUCT or ??
 

Hi,
I think I want to use an array but I am not sure if I do, and if I do need
one, how to use it, besides the CTRL-shift-enter. And, I am curious why
CTRL-shift-enter is required and why not just using enter works.

I have a 2 columns of data. The left-most column is formatted as text with
either an invoice number or one of the following 3 text phrases: Initial PO
Amount, PO Revision, or Balance Remaining. The right-side column is
formatted as a number as it has the text column entry's corresponding dollar
amount. I want to add the dollar amounts based on what is in the text
column. As an example, if the text entry is either "Initial PO Amount" or
"PO Revision" I want the corresponding amounts added together. And, if the
text entry does not say "Initial PO Amount" or "PO Revision" I want the
dollar amounts added together. I can completely ignore the text phrase
"Balance Remaining"

Thanks for the help.


Invoice Amount
Initial PO Amount 10,000
01-01 50
01-02 275
02-01 9,500
Balance Remaining 175
PO Revision 5,000
02-02 1,200
03-01 3,225
Balance Remaining 750
PO Revision 3,000
Balance Remaining 3,750


I am thinking an array is needed to determine the next two lines, and a
standard formaul to get the available amount
Total PO Amount 18,000
Total Invoices 14,250
Available Amount 3,750

SUMIF or SUMPRODUCT or ??
 

=SUMIF(A1:A15,"Initial PO Amount",B1:B15)
=SUMPRODUCT(N(NOT(ISERROR(FIND("-",A1:A15)))),B1:B15)

"Stumped" wrote in message
...
Hi,
I think I want to use an array but I am not sure if I do, and if I do need
one, how to use it, besides the CTRL-shift-enter. And, I am curious why
CTRL-shift-enter is required and why not just using enter works.

I have a 2 columns of data. The left-most column is formatted as text
with
either an invoice number or one of the following 3 text phrases: Initial
PO
Amount, PO Revision, or Balance Remaining. The right-side column is
formatted as a number as it has the text column entry's corresponding
dollar
amount. I want to add the dollar amounts based on what is in the text
column. As an example, if the text entry is either "Initial PO Amount" or
"PO Revision" I want the corresponding amounts added together. And, if
the
text entry does not say "Initial PO Amount" or "PO Revision" I want the
dollar amounts added together. I can completely ignore the text phrase
"Balance Remaining"

Thanks for the help.


Invoice Amount
Initial PO Amount 10,000
01-01 50
01-02 275
02-01 9,500
Balance Remaining 175
PO Revision 5,000
02-02 1,200
03-01 3,225
Balance Remaining 750
PO Revision 3,000
Balance Remaining 3,750


I am thinking an array is needed to determine the next two lines, and a
standard formaul to get the available amount
Total PO Amount 18,000
Total Invoices 14,250
Available Amount 3,750

SUMIF or SUMPRODUCT or ??
 

Thanks. Your answer did it.

"Bob Umlas" wrote:

=SUMIF(A1:A15,"Initial PO Amount",B1:B15)
=SUMPRODUCT(N(NOT(ISERROR(FIND("-",A1:A15)))),B1:B15)

"Stumped" wrote in message
...
Hi,
I think I want to use an array but I am not sure if I do, and if I do need
one, how to use it, besides the CTRL-shift-enter. And, I am curious why
CTRL-shift-enter is required and why not just using enter works.

I have a 2 columns of data. The left-most column is formatted as text
with
either an invoice number or one of the following 3 text phrases: Initial
PO
Amount, PO Revision, or Balance Remaining. The right-side column is
formatted as a number as it has the text column entry's corresponding
dollar
amount. I want to add the dollar amounts based on what is in the text
column. As an example, if the text entry is either "Initial PO Amount" or
"PO Revision" I want the corresponding amounts added together. And, if
the
text entry does not say "Initial PO Amount" or "PO Revision" I want the
dollar amounts added together. I can completely ignore the text phrase
"Balance Remaining"

Thanks for the help.


Invoice Amount
Initial PO Amount 10,000
01-01 50
01-02 275
02-01 9,500
Balance Remaining 175
PO Revision 5,000
02-02 1,200
03-01 3,225
Balance Remaining 750
PO Revision 3,000
Balance Remaining 3,750


I am thinking an array is needed to determine the next two lines, and a
standard formaul to get the available amount
Total PO Amount 18,000
Total Invoices 14,250
Available Amount 3,750