I am having problems trying to create an equation to count the number of
instances a certain situation occurs. It is quite complicated and have been
unable to find any working answers when searching the forums or even when
using the live microsoft chat help.

In my table I am monitoring the progress of reports. The report is sent to
an affiliate and they return a set tracking form to say whether they have
submitted it or not. I have the added problem that I am working with
government regulations, so as this table will be featured in a report of it's
own, I cannot change the layout of the table.

The 3 relevant colunms include;
A - date (if the report is submitted),
B - "x" if the report is not submitted, (it is left blank if unknown or
if it has been submitted)
C - "x" if we have not received the tracking sheet (also left blank if
otherwise)

It can occur that we know through a another source (eg email) that the
report has been submitted or not even though we have received no tracking
form.
Eg - Column A would contain a date, and Colum C would have a "x" (did not
received tracking sheet but report was submitted)
- Column B would contain an "x" and column C would contain an "x" (did
not received tracking sheet but report was not submitted)

So I need to count the number of times situations like this have occured. I
have tried all sorts of functions such as COUNTA, SUMPRODUCT, etc and
obviously I have the wrong idea.

I hope this is clear
I would really appreciate some help with this as it has already swallowed a
lot of my time!
Thank you in advance

To count how many times you have an X in both B and C
=SUMPRODUCT(--(B1:B100="x"),--(C1:C100="x"))
To count how many times you have an X in C and B is blank
=SUMPRODUCT(--(B1:B100=""),--(C1:C100="x"))

Unless you have Excel 2007 do NOT use full column references as in
=SUMPRODUCT(--(B:B="x"),--(C:C="x"))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Joanne" wrote in message
...
I am having problems trying to create an equation to count the number of
instances a certain situation occurs. It is quite complicated and have
been
unable to find any working answers when searching the forums or even when
using the live microsoft chat help.

In my table I am monitoring the progress of reports. The report is sent to
an affiliate and they return a set tracking form to say whether they have
submitted it or not. I have the added problem that I am working with
government regulations, so as this table will be featured in a report of
it's
own, I cannot change the layout of the table.

The 3 relevant colunms include;
A - date (if the report is submitted),
B - "x" if the report is not submitted, (it is left blank if unknown
or
if it has been submitted)
C - "x" if we have not received the tracking sheet (also left blank if
otherwise)

It can occur that we know through a another source (eg email) that the
report has been submitted or not even though we have received no tracking
form.
Eg - Column A would contain a date, and Colum C would have a "x" (did not
received tracking sheet but report was submitted)
- Column B would contain an "x" and column C would contain an "x"
(did
not received tracking sheet but report was not submitted)

So I need to count the number of times situations like this have occured.
I
have tried all sorts of functions such as COUNTA, SUMPRODUCT, etc and
obviously I have the wrong idea.

I hope this is clear
I would really appreciate some help with this as it has already swallowed
a
lot of my time!
Thank you in advance

Hi Joanne, first check enter

=SUMPRODUCT(--(A1:A11<""),--(C1:C11="x"))

2nd formula


=SUMPRODUCT(--(B1:B6="x"),--(C1:C6="x"))

Change the range to your needs


"Joanne" wrote:

I am having problems trying to create an equation to count the number of
instances a certain situation occurs. It is quite complicated and have been
unable to find any working answers when searching the forums or even when
using the live microsoft chat help.

In my table I am monitoring the progress of reports. The report is sent to
an affiliate and they return a set tracking form to say whether they have
submitted it or not. I have the added problem that I am working with
government regulations, so as this table will be featured in a report of it's
own, I cannot change the layout of the table.

The 3 relevant colunms include;
A - date (if the report is submitted),
B - "x" if the report is not submitted, (it is left blank if unknown or
if it has been submitted)
C - "x" if we have not received the tracking sheet (also left blank if
otherwise)

It can occur that we know through a another source (eg email) that the
report has been submitted or not even though we have received no tracking
form.
Eg - Column A would contain a date, and Colum C would have a "x" (did not
received tracking sheet but report was submitted)
- Column B would contain an "x" and column C would contain an "x" (did
not received tracking sheet but report was not submitted)

So I need to count the number of times situations like this have occured. I
have tried all sorts of functions such as COUNTA, SUMPRODUCT, etc and
obviously I have the wrong idea.

I hope this is clear
I would really appreciate some help with this as it has already swallowed a
lot of my time!
Thank you in advance

Thank you that was very usefull. Is there a way to combine the two into one
equation or is that over complicating things?

"Eduardo" wrote:

Hi Joanne, first check enter

=SUMPRODUCT(--(A1:A11<""),--(C1:C11="x"))

2nd formula


=SUMPRODUCT(--(B1:B6="x"),--(C1:C6="x"))

Change the range to your needs


"Joanne" wrote:

I am having problems trying to create an equation to count the number of
instances a certain situation occurs. It is quite complicated and have been
unable to find any working answers when searching the forums or even when
using the live microsoft chat help.

In my table I am monitoring the progress of reports. The report is sent to
an affiliate and they return a set tracking form to say whether they have
submitted it or not. I have the added problem that I am working with
government regulations, so as this table will be featured in a report of it's
own, I cannot change the layout of the table.

The 3 relevant colunms include;
A - date (if the report is submitted),
B - "x" if the report is not submitted, (it is left blank if unknown or
if it has been submitted)
C - "x" if we have not received the tracking sheet (also left blank if
otherwise)

It can occur that we know through a another source (eg email) that the
report has been submitted or not even though we have received no tracking
form.
Eg - Column A would contain a date, and Colum C would have a "x" (did not
received tracking sheet but report was submitted)
- Column B would contain an "x" and column C would contain an "x" (did
not received tracking sheet but report was not submitted)

So I need to count the number of times situations like this have occured. I
have tried all sorts of functions such as COUNTA, SUMPRODUCT, etc and
obviously I have the wrong idea.

I hope this is clear
I would really appreciate some help with this as it has already swallowed a
lot of my time!
Thank you in advance

Hi Joanne,
if the answer was helpfull could you please rate it on the left bottom
corner thank you

"Joanne" wrote:

Thank you that was very usefull. Is there a way to combine the two into one
equation or is that over complicating things?

"Eduardo" wrote:

Hi Joanne, first check enter

=SUMPRODUCT(--(A1:A11<""),--(C1:C11="x"))

2nd formula


=SUMPRODUCT(--(B1:B6="x"),--(C1:C6="x"))

Change the range to your needs


"Joanne" wrote:

I am having problems trying to create an equation to count the number of
instances a certain situation occurs. It is quite complicated and have been
unable to find any working answers when searching the forums or even when
using the live microsoft chat help.

In my table I am monitoring the progress of reports. The report is sent to
an affiliate and they return a set tracking form to say whether they have
submitted it or not. I have the added problem that I am working with
government regulations, so as this table will be featured in a report of it's
own, I cannot change the layout of the table.

The 3 relevant colunms include;
A - date (if the report is submitted),
B - "x" if the report is not submitted, (it is left blank if unknown or
if it has been submitted)
C - "x" if we have not received the tracking sheet (also left blank if
otherwise)

It can occur that we know through a another source (eg email) that the
report has been submitted or not even though we have received no tracking
form.
Eg - Column A would contain a date, and Colum C would have a "x" (did not
received tracking sheet but report was submitted)
- Column B would contain an "x" and column C would contain an "x" (did
not received tracking sheet but report was not submitted)

So I need to count the number of times situations like this have occured. I
have tried all sorts of functions such as COUNTA, SUMPRODUCT, etc and
obviously I have the wrong idea.

I hope this is clear
I would really appreciate some help with this as it has already swallowed a
lot of my time!
Thank you in advance