Formulas Still Not Working

[Michelle]

I am so sorry to bother everyone, but would love it if someone could
contunie to help me with these formulas. I made the initial inquiry
below, and Isaeblle provided me with formulas (so appreciate the
help), but they are not working in my spreadsheet. I am not an excel
person at all - a coworker was terminated, and my boss has asked me to
help her put calcuations into a spreadsheet that is due to accounting
this afternoon. I was referred to this site as a possible solution. I
am using Excel 07.

=SUMPRODUCT(--(A2:A20="Open - Active")*(B2:B20<>"Gvt
Invest")*(C2:C120))


=SUMPRODUCT(--(A2:A20="Open - Active")*(B2:B20="Gvt
Invest")*(C2:C120))


=SUMPRODUCT(--(A2:A20="Open - Probable")*(C2:C120))

See example and explanation below:

STATUS NAT OF CASE RSV

Open - Active EEOC 1000
Open - Probable EEOC 1000
Open - Active GVT Invest 1000
Open - Probable Gvt Invest 2000


How do I create formulas that will capture the subtotals totals for J
for:

Open - Active (Non - Government Investigations): Total all in
spreadsheet that are Open - Active but not classified as Gvt invest.
Total should equal 1000

Open - Active (Government Investigations): Total all in spreadsheet
that are Open - Active but classified as Gvt invest. only. Total
should equal 1000

Open - Probable: Totals all in spreadsheet that are Open - Probable.
Total should equal 3000

 

[Gord Dibben]

"not working" means what? Wrong answer? Nothing? Error?

I tested my own and Isabelle's formulas on your sample data.

Return all the values you ask for in your 3 scenarios

1000, 1000, 3000

In your first post you showed your data in columns F, G and H

I based my formula on that sample.

Where is your data? Use a range when posting back.

By range I mean A2:C20 or F2:H10 or similar.

We are assuming you have a header row for titles.


Gord

 

[Michelle]

"not working" means what?   Wrong answer?  Nothing?   Error?

I tested my own and Isabelle's formulas on your sample data.

Return all the values you ask for in your 3 scenarios

1000,   1000,   3000

In your first post you showed your data in columns F, G and H

I based my formula on that sample.

Where is your data?   Use a range when posting back.

By range I mean   A2:C20    or F2:H10    or similar.

We are assuming you have a header row for titles.

Gord













- Show quoted text -

Thanks. The result I get is #VALUE. I do have header Rows. I ahve many
more columns, but provided only the ones I thought were needed for the
calculation. My rows (with data) start at 7 (Header is row 6) and
contiune through 159 (so 152 total rows of data), but could grow
beyond 156. My columns are A through P, so I guess that means my range
is A7159 (but could be larger if more data were added? I want the
result of the 3 formulas to populate in C1, C2 and C3 respectively, so
that is where I entered the formula. A few things have been changed in
the table and from the example I provided, but I corrected those in
the formula. I have updated it below to be an accurate description,
but I confirmed that the changes I hade to your formula comported.
Sorry to be such a problem. Does this help? Im sure Im doing something
wrong.....


F
G K7 >Open - Active
EEOC $1000
8> >Open - Probable
EEOC $1000
9> >Open - Active Government
Inquiry $1000
10> >Open - Probable Government
Inquiry $2000

So, in C1 (Open - Active (Non Government Inquiries): I put in the
following formula: =SUMPRODUCT(--(F7:F1000="Open -
Active")*(G7:G1000<>"Government Inquiry")*(K7:K1000))

In C2 (Open - Active (Government Inquiries): I put =SUMPRODUCT(--
(F7:F1000="Open - Active")*(G7:G1000="Government Inquiry")*(K7:K1000))

In c3 (Open - Probable): I put =SUMPRODUCT(--(F7:F1000="Open -
Probable")*(F7:F1000))

 

[Claus Busch]

Hi Michelle,

Am Fri, 6 Jan 2012 12:02:46 -0800 (PST) schrieb Michelle:

Thanks. The result I get is #VALUE.

try:
=SUMPRODUCT(--(A1:A20="Open - Active"),--(B1:B20="GVT Invest"),C1:C20)


Regards
Claus Busch

 

[Michelle]

Hi Michelle,

Am Fri, 6 Jan 2012 12:02:46 -0800 (PST) schrieb Michelle:


try:
=SUMPRODUCT(--(A1:A20="Open - Active"),--(B1:B20="GVT Invest"),C1:C20)

Regards
Claus Busch

Claus - this worked! I placed the formula in all chree cells, and
updated to capture the ranges I need, etc., and the calculations work
correctly. Truly, from the bottom of my heart - I thank you ALL! I
also think Im going to take an excel class! You are all amazing, and I
wish I could thank you all in person!

 

[joeu2004]

Am Fri, 6 Jan 2012 12:02:46 -0800 (PST) schrieb Michelle:

try:
=SUMPRODUCT(--(A1:A20="Open - Active"),--(B1:B20="GVT Invest"),C1:C20)

Or to simplify:

=SUMPRODUCT((A1:A20="Open - Active")*(B1:B20="GVT Invest"),C1:C20)

Things to note:

1. You do not need the double negative if you use multiplication in the
arithemetic expression. The purpose of the double negative in this context
is to convert TRUE and FALSE into 1 and 0. But any arithmetic operation
will accomplish that.

2. Separate C1:C20 with a comma (separate parameter), instead of multiplying
it in the arithmetic expression. The effect is the same. The difference
is: SUMPRODUCT can recognize and ignore non-numeric data in C1:C20. When
you multiply C1:C20, Excel's arithmetic expression evaluator does not
tolerate non-numeric data.