On Jan 6, 1:30*pm, Gord Dibben wrote:
"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

On Fri, 6 Jan 2012 11:08:35 -0800 (PST), Michelle



wrote:
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- Hide quoted text -

- 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 A7:P159 (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 K
STATUS STATUS RSV
7 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))