=COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR")

The above calculation does not work. Can anyone help me? I need to count
the number of exceptions in a range of columns skipping column "I" if the
persons initials in a range in column "D" equals their initials.

Thanks.
--
Deb

Actually the range I am trying to look at is G4:G10/H4:H10/J4:J10/and K4:K10.
Each column has a different responsibility held by the person and the
responsibilites are assigned to varying functions defined by what they fall
under Compliance, Underwriting, and Other.

As I am reading through here I am wondering if SUMPRODUCT is not what I
need. Any help would be appreciated.

Thanks.
--
Deb


"Deb" wrote:

=COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR")

The above calculation does not work. Can anyone help me? I need to count
the number of exceptions in a range of columns skipping column "I" if the
persons initials in a range in column "D" equals their initials.

Thanks.
--
Deb

Please don't post more than once, stay in the original thread.
The regulars will find your post

As I posted to your other post you cannot use that function with ranges that
are not equal in size

You can try


=SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100))


or


=SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130))

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
=COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR")

The above calculation does not work. Can anyone help me? I need to count
the number of exceptions in a range of columns skipping column "I" if the
persons initials in a range in column "D" equals their initials.

Thanks.
--
Deb

Sorry, I was hoping for someone to respond so I went to another catagory.

I tried your suggestion and am getting a value but it is not the one I was
hoping for. Below is the formula.

SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMPR ODUCT(($D$4:$D$10="DS")*($J$4:$K$10))

The results are that "DS" occurs 2x on D5 and D10 and H5=1 and H10=2 and
J10=2.

I am trying to cound the number of times the exceptions are "0" for each
loan that person processed. Each row is a loan. The errors are defined in a
range of G4:H10 and J4:K10. I then will divide the number of occurences per
the number of loans that "DS" processed - which in the case above should be
2/2 = 100% Error rate. Therefore, if they processed 5 loans and only 2 loans
had Compliance exceptions it would be 2/5=40% error rate.
--
Deb


"Peo Sjoblom" wrote:

Please don't post more than once, stay in the original thread.
The regulars will find your post

As I posted to your other post you cannot use that function with ranges that
are not equal in size

You can try


=SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100))


or


=SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130))

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
=COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR")

The above calculation does not work. Can anyone help me? I need to count
the number of exceptions in a range of columns skipping column "I" if the
persons initials in a range in column "D" equals their initials.

Thanks.
--
Deb

What is the result you get, if I create a dummy sheet and just put in "DS"
in D5 and D10
and 1 in H5 and 2 in H10 I get the result of 2 which I assume you want. If
you get something else than
you might have extra spaces in D etc.

=SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($G$4:$H$100) )+SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($J$4:$K$10))

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
Sorry, I was hoping for someone to respond so I went to another catagory.

I tried your suggestion and am getting a value but it is not the one I was
hoping for. Below is the formula.

SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMPR ODUCT(($D$4:$D$10="DS")*($J$4:$K$10))

The results are that "DS" occurs 2x on D5 and D10 and H5=1 and H10=2 and
J10=2.

I am trying to cound the number of times the exceptions are "0" for each
loan that person processed. Each row is a loan. The errors are defined
in a
range of G4:H10 and J4:K10. I then will divide the number of occurences
per
the number of loans that "DS" processed - which in the case above should
be
2/2 = 100% Error rate. Therefore, if they processed 5 loans and only 2
loans
had Compliance exceptions it would be 2/5=40% error rate.
--
Deb


"Peo Sjoblom" wrote:

Please don't post more than once, stay in the original thread.
The regulars will find your post

As I posted to your other post you cannot use that function with ranges
that
are not equal in size

You can try


=SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100))


or


=SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130))

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
=COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR")

The above calculation does not work. Can anyone help me? I need to
count
the number of exceptions in a range of columns skipping column "I" if
the
persons initials in a range in column "D" equals their initials.

Thanks.
--
Deb

My spreadsheet is showing "4".
--
Deb


"Peo Sjoblom" wrote:

What is the result you get, if I create a dummy sheet and just put in "DS"
in D5 and D10
and 1 in H5 and 2 in H10 I get the result of 2 which I assume you want. If
you get something else than
you might have extra spaces in D etc.

=SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($G$4:$H$100) )+SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($J$4:$K$10))

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
Sorry, I was hoping for someone to respond so I went to another catagory.

I tried your suggestion and am getting a value but it is not the one I was
hoping for. Below is the formula.

SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMPR ODUCT(($D$4:$D$10="DS")*($J$4:$K$10))

The results are that "DS" occurs 2x on D5 and D10 and H5=1 and H10=2 and
J10=2.

I am trying to cound the number of times the exceptions are "0" for each
loan that person processed. Each row is a loan. The errors are defined
in a
range of G4:H10 and J4:K10. I then will divide the number of occurences
per
the number of loans that "DS" processed - which in the case above should
be
2/2 = 100% Error rate. Therefore, if they processed 5 loans and only 2
loans
had Compliance exceptions it would be 2/5=40% error rate.
--
Deb


"Peo Sjoblom" wrote:

Please don't post more than once, stay in the original thread.
The regulars will find your post

As I posted to your other post you cannot use that function with ranges
that
are not equal in size

You can try


=SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100))


or


=SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130))

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
=COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR")

The above calculation does not work. Can anyone help me? I need to
count
the number of exceptions in a range of columns skipping column "I" if
the
persons initials in a range in column "D" equals their initials.

Thanks.
--
Deb

Do you have anything in G5, G10, J5, J10, K5, K10 that is greater than zero?

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
My spreadsheet is showing "4".
--
Deb


"Peo Sjoblom" wrote:

What is the result you get, if I create a dummy sheet and just put in
"DS"
in D5 and D10
and 1 in H5 and 2 in H10 I get the result of 2 which I assume you want.
If
you get something else than
you might have extra spaces in D etc.

=SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($G$4:$H$100) )+SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($J$4:$K$10))

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
Sorry, I was hoping for someone to respond so I went to another
catagory.

I tried your suggestion and am getting a value but it is not the one I
was
hoping for. Below is the formula.

SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMPR ODUCT(($D$4:$D$10="DS")*($J$4:$K$10))

The results are that "DS" occurs 2x on D5 and D10 and H5=1 and H10=2
and
J10=2.

I am trying to cound the number of times the exceptions are "0" for
each
loan that person processed. Each row is a loan. The errors are
defined
in a
range of G4:H10 and J4:K10. I then will divide the number of
occurences
per
the number of loans that "DS" processed - which in the case above
should
be
2/2 = 100% Error rate. Therefore, if they processed 5 loans and only 2
loans
had Compliance exceptions it would be 2/5=40% error rate.
--
Deb


"Peo Sjoblom" wrote:

Please don't post more than once, stay in the original thread.
The regulars will find your post

As I posted to your other post you cannot use that function with
ranges
that
are not equal in size

You can try


=SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100))


or


=SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130))

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
=COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR")

The above calculation does not work. Can anyone help me? I need to
count
the number of exceptions in a range of columns skipping column "I"
if
the
persons initials in a range in column "D" equals their initials.

Thanks.
--
Deb