that adds the rows with text (landscape contracts) in column I; rows(I7:I23),
but also need to subtract that total number of (landscape contracts) from
columns A (a7:a23) and column F (f7:f23) that contain an "X", indicating the
contracts have been "discontinued" or are "not participating". In this case,
I have 17 contracts and can get the formula to tell me that, but am having
trouble getting the formula to look thru column A and F and find the "X" and
to subtract those contracts to get me my total. It should pick up two "X"'s
and subtract them from 17 contracts to give me 15 total. Can anyone help.
Thanks

Hi,
try

=SUMPRODUCT((f7:f23<"X")+(a7:a23<""))

"Geauxfish" wrote:

that adds the rows with text (landscape contracts) in column I; rows(I7:I23),
but also need to subtract that total number of (landscape contracts) from
columns A (a7:a23) and column F (f7:f23) that contain an "X", indicating the
contracts have been "discontinued" or are "not participating". In this case,
I have 17 contracts and can get the formula to tell me that, but am having
trouble getting the formula to look thru column A and F and find the "X" and
to subtract those contracts to get me my total. It should pick up two "X"'s
and subtract them from 17 contracts to give me 15 total. Can anyone help.
Thanks

Perfect! It worked great.
You sure know your Excel language!
How do I find out what all the symbols mean like <, ?, , $, etc. I havn't
a clue.
Thanks Eduardo

"Eduardo" wrote:

Hi,
try

=SUMPRODUCT((f7:f23<"X")+(a7:a23<""))

"Geauxfish" wrote:

that adds the rows with text (landscape contracts) in column I; rows(I7:I23),
but also need to subtract that total number of (landscape contracts) from
columns A (a7:a23) and column F (f7:f23) that contain an "X", indicating the
contracts have been "discontinued" or are "not participating". In this case,
I have 17 contracts and can get the formula to tell me that, but am having
trouble getting the formula to look thru column A and F and find the "X" and
to subtract those contracts to get me my total. It should pick up two "X"'s
and subtract them from 17 contracts to give me 15 total. Can anyone help.
Thanks

Well I spoke too soon...
the formula works, but if I add a "X" in column A, say A11, it then proceeds
to add that to the formula instead of subtracting. It now says 16 total,
when it should say 14.

"Eduardo" wrote:

Hi,
try

=SUMPRODUCT((f7:f23<"X")+(a7:a23<""))

"Geauxfish" wrote:

that adds the rows with text (landscape contracts) in column I; rows(I7:I23),
but also need to subtract that total number of (landscape contracts) from
columns A (a7:a23) and column F (f7:f23) that contain an "X", indicating the
contracts have been "discontinued" or are "not participating". In this case,
I have 17 contracts and can get the formula to tell me that, but am having
trouble getting the formula to look thru column A and F and find the "X" and
to subtract those contracts to get me my total. It should pick up two "X"'s
and subtract them from 17 contracts to give me 15 total. Can anyone help.
Thanks

I think this fixed it,

=SUMPRODUCT((F7:F23<"X")*(A7:A23<"X"))

"Geauxfish" wrote:

that adds the rows with text (landscape contracts) in column I; rows(I7:I23),
but also need to subtract that total number of (landscape contracts) from
columns A (a7:a23) and column F (f7:f23) that contain an "X", indicating the
contracts have been "discontinued" or are "not participating". In this case,
I have 17 contracts and can get the formula to tell me that, but am having
trouble getting the formula to look thru column A and F and find the "X" and
to subtract those contracts to get me my total. It should pick up two "X"'s
and subtract them from 17 contracts to give me 15 total. Can anyone help.
Thanks

Hi,
But as per your post the X is in column F, if you add an X in column A it
will add to the total because column F is empty

"Geauxfish" wrote:

Well I spoke too soon...
the formula works, but if I add a "X" in column A, say A11, it then proceeds
to add that to the formula instead of subtracting. It now says 16 total,
when it should say 14.

"Eduardo" wrote:

Hi,
try

=SUMPRODUCT((f7:f23<"X")+(a7:a23<""))

"Geauxfish" wrote:

that adds the rows with text (landscape contracts) in column I; rows(I7:I23),
but also need to subtract that total number of (landscape contracts) from
columns A (a7:a23) and column F (f7:f23) that contain an "X", indicating the
contracts have been "discontinued" or are "not participating". In this case,
I have 17 contracts and can get the formula to tell me that, but am having
trouble getting the formula to look thru column A and F and find the "X" and
to subtract those contracts to get me my total. It should pick up two "X"'s
and subtract them from 17 contracts to give me 15 total. Can anyone help.
Thanks

Hi,
Be careful your column A it supposed to be the number of contracts as per
your post and column F where you put an X to indicate that the project has
been discontinued
In the formula you sent you are entering X in column A and not counting it

"Geauxfish" wrote:

I think this fixed it,

=SUMPRODUCT((F7:F23<"X")*(A7:A23<"X"))

"Geauxfish" wrote:

that adds the rows with text (landscape contracts) in column I; rows(I7:I23),
but also need to subtract that total number of (landscape contracts) from
columns A (a7:a23) and column F (f7:f23) that contain an "X", indicating the
contracts have been "discontinued" or are "not participating". In this case,
I have 17 contracts and can get the formula to tell me that, but am having
trouble getting the formula to look thru column A and F and find the "X" and
to subtract those contracts to get me my total. It should pick up two "X"'s
and subtract them from 17 contracts to give me 15 total. Can anyone help.
Thanks

Sorry I didn't provide more info. Column A was "discontinued" marked with an
"X" and column F was "not participating" marked with an "X" also. Column I
was counting the names of the contract by rows with text. I had to show
where the total contracts (of column I) were subtracted from either/or
columns A and F with an "X". Your formula worked, but I tweaked it to work
when adding an "X" to either column A or F when finding out that particular
contract was discontinued for FY 2009-2010.

="DISTRICT TOTALS "&SUMPRODUCT((F7:F23<"X")*(A7:A23<"X"))

this is how the formula worked.
Thanks again.

"Eduardo" wrote:

Hi,
Be careful your column A it supposed to be the number of contracts as per
your post and column F where you put an X to indicate that the project has
been discontinued
In the formula you sent you are entering X in column A and not counting it

"Geauxfish" wrote:

I think this fixed it,

=SUMPRODUCT((F7:F23<"X")*(A7:A23<"X"))

"Geauxfish" wrote:

that adds the rows with text (landscape contracts) in column I; rows(I7:I23),
but also need to subtract that total number of (landscape contracts) from
columns A (a7:a23) and column F (f7:f23) that contain an "X", indicating the
contracts have been "discontinued" or are "not participating". In this case,
I have 17 contracts and can get the formula to tell me that, but am having
trouble getting the formula to look thru column A and F and find the "X" and
to subtract those contracts to get me my total. It should pick up two "X"'s
and subtract them from 17 contracts to give me 15 total. Can anyone help.
Thanks

a new question...
In column S, I have total cost on contracts, but the formula I am using
=SUM(S7:S23) totals all, but I need it to remove row 9 and 22 cause they were
identified in column F as "not participating". How would the formula be that
adds all amounts in column S and subtracts row 9 and 22 under the column F
identified with an "X" as in previous question.

"Eduardo" wrote:

Hi,
Be careful your column A it supposed to be the number of contracts as per
your post and column F where you put an X to indicate that the project has
been discontinued
In the formula you sent you are entering X in column A and not counting it

"Geauxfish" wrote:

I think this fixed it,

=SUMPRODUCT((F7:F23<"X")*(A7:A23<"X"))

"Geauxfish" wrote:

that adds the rows with text (landscape contracts) in column I; rows(I7:I23),
but also need to subtract that total number of (landscape contracts) from
columns A (a7:a23) and column F (f7:f23) that contain an "X", indicating the
contracts have been "discontinued" or are "not participating". In this case,
I have 17 contracts and can get the formula to tell me that, but am having
trouble getting the formula to look thru column A and F and find the "X" and
to subtract those contracts to get me my total. It should pick up two "X"'s
and subtract them from 17 contracts to give me 15 total. Can anyone help.
Thanks

Hi,
The formula will be
=sumproduct(--(F7:F23<"X"),S7:S23)

if you need to take in consideration the X in column A as well

=sumproduct(--(F7:F23<"X"),--(A7:A23<"X"),S7:S23)

"Geauxfish" wrote:

a new question...
In column S, I have total cost on contracts, but the formula I am using
=SUM(S7:S23) totals all, but I need it to remove row 9 and 22 cause they were
identified in column F as "not participating". How would the formula be that
adds all amounts in column S and subtracts row 9 and 22 under the column F
identified with an "X" as in previous question.

"Eduardo" wrote:

Hi,
Be careful your column A it supposed to be the number of contracts as per
your post and column F where you put an X to indicate that the project has
been discontinued
In the formula you sent you are entering X in column A and not counting it

"Geauxfish" wrote:

I think this fixed it,

=SUMPRODUCT((F7:F23<"X")*(A7:A23<"X"))

"Geauxfish" wrote:

that adds the rows with text (landscape contracts) in column I; rows(I7:I23),
but also need to subtract that total number of (landscape contracts) from
columns A (a7:a23) and column F (f7:f23) that contain an "X", indicating the
contracts have been "discontinued" or are "not participating". In this case,
I have 17 contracts and can get the formula to tell me that, but am having
trouble getting the formula to look thru column A and F and find the "X" and
to subtract those contracts to get me my total. It should pick up two "X"'s
and subtract them from 17 contracts to give me 15 total. Can anyone help.
Thanks

Hi,
you can start taking a look at Debra web is excellent and have lots of
examples

http://www.contextures.com/tiptech.html

"Geauxfish" wrote:

Perfect! It worked great.
You sure know your Excel language!
How do I find out what all the symbols mean like <, ?, , $, etc. I havn't
a clue.
Thanks Eduardo

"Eduardo" wrote:

Hi,
try

=SUMPRODUCT((f7:f23<"X")+(a7:a23<""))

"Geauxfish" wrote:

that adds the rows with text (landscape contracts) in column I; rows(I7:I23),
but also need to subtract that total number of (landscape contracts) from
columns A (a7:a23) and column F (f7:f23) that contain an "X", indicating the
contracts have been "discontinued" or are "not participating". In this case,
I have 17 contracts and can get the formula to tell me that, but am having
trouble getting the formula to look thru column A and F and find the "X" and
to subtract those contracts to get me my total. It should pick up two "X"'s
and subtract them from 17 contracts to give me 15 total. Can anyone help.
Thanks

the formula won't work because I have #DIV/0! in the column S, row 9 and that
is due to that contract "not participating". Can the formula be written to
just overlook the rows instead of summing the entire column S. I was trying
do it where the column A:9 and F:22 that has an "X" would overlook or
eliminate the their respective row within the formula. I hope it's not to
confusing of explanation.

"Eduardo" wrote:

Hi,
The formula will be
=sumproduct(--(F7:F23<"X"),S7:S23)

if you need to take in consideration the X in column A as well

=sumproduct(--(F7:F23<"X"),--(A7:A23<"X"),S7:S23)

"Geauxfish" wrote:

a new question...
In column S, I have total cost on contracts, but the formula I am using
=SUM(S7:S23) totals all, but I need it to remove row 9 and 22 cause they were
identified in column F as "not participating". How would the formula be that
adds all amounts in column S and subtracts row 9 and 22 under the column F
identified with an "X" as in previous question.

"Eduardo" wrote:

Hi,
Be careful your column A it supposed to be the number of contracts as per
your post and column F where you put an X to indicate that the project has
been discontinued
In the formula you sent you are entering X in column A and not counting it

"Geauxfish" wrote:

I think this fixed it,

=SUMPRODUCT((F7:F23<"X")*(A7:A23<"X"))

"Geauxfish" wrote:

that adds the rows with text (landscape contracts) in column I; rows(I7:I23),
but also need to subtract that total number of (landscape contracts) from
columns A (a7:a23) and column F (f7:f23) that contain an "X", indicating the
contracts have been "discontinued" or are "not participating". In this case,
I have 17 contracts and can get the formula to tell me that, but am having
trouble getting the formula to look thru column A and F and find the "X" and
to subtract those contracts to get me my total. It should pick up two "X"'s
and subtract them from 17 contracts to give me 15 total. Can anyone help.
Thanks

Hi,try changing the formula in S, for

=IF(Q8="X","",+Q8/R8)

change the cells to fit your needs, that will eliminate the #DIV and replace
it for a blank space

"Geauxfish" wrote:

the formula won't work because I have #DIV/0! in the column S, row 9 and that
is due to that contract "not participating". Can the formula be written to
just overlook the rows instead of summing the entire column S. I was trying
do it where the column A:9 and F:22 that has an "X" would overlook or
eliminate the their respective row within the formula. I hope it's not to
confusing of explanation.

"Eduardo" wrote:

Hi,
The formula will be
=sumproduct(--(F7:F23<"X"),S7:S23)

if you need to take in consideration the X in column A as well

=sumproduct(--(F7:F23<"X"),--(A7:A23<"X"),S7:S23)

"Geauxfish" wrote:

a new question...
In column S, I have total cost on contracts, but the formula I am using
=SUM(S7:S23) totals all, but I need it to remove row 9 and 22 cause they were
identified in column F as "not participating". How would the formula be that
adds all amounts in column S and subtracts row 9 and 22 under the column F
identified with an "X" as in previous question.

"Eduardo" wrote:

Hi,
Be careful your column A it supposed to be the number of contracts as per
your post and column F where you put an X to indicate that the project has
been discontinued
In the formula you sent you are entering X in column A and not counting it

"Geauxfish" wrote:

I think this fixed it,

=SUMPRODUCT((F7:F23<"X")*(A7:A23<"X"))

"Geauxfish" wrote:

that adds the rows with text (landscape contracts) in column I; rows(I7:I23),
but also need to subtract that total number of (landscape contracts) from
columns A (a7:a23) and column F (f7:f23) that contain an "X", indicating the
contracts have been "discontinued" or are "not participating". In this case,
I have 17 contracts and can get the formula to tell me that, but am having
trouble getting the formula to look thru column A and F and find the "X" and
to subtract those contracts to get me my total. It should pick up two "X"'s
and subtract them from 17 contracts to give me 15 total. Can anyone help.
Thanks