COUNTIF help
 

=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

COUNTIF help
 

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

COUNTIF help
 

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

COUNTIF help
 

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

COUNTIF help
 

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

COUNTIF help
 

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

COUNTIF help
 

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

COUNTIF help
 

Thanks for all your help on this.

Yes. I think I had stated earlier that I had a 2 in J10.
--
Deb


"Peo Sjoblom" wrote:

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

COUNTIF help
 

You certainly did, I somehow missed the J10. Try this

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


I missed the second 0 in my original formula, this shows 3 now

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
Thanks for all your help on this.

Yes. I think I had stated earlier that I had a 2 in J10.
--
Deb


"Peo Sjoblom" wrote:

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

COUNTIF help
 

That does help, but I need to only show 2. If multiple exceptions appear on
the same row I still only want to count it as 1 to make it calculate
2/2=100%. Is there another conditional statement I could add?
--
Deb


"Peo Sjoblom" wrote:

You certainly did, I somehow missed the J10. Try this

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


I missed the second 0 in my original formula, this shows 3 now

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
Thanks for all your help on this.

Yes. I think I had stated earlier that I had a 2 in J10.
--
Deb


"Peo Sjoblom" wrote:

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

COUNTIF help
 

There might be something fairly complicated, I'll post back when I have
played a round a bit
and if I find something.
However it is not the best of spreadsheet designs. You could easily solve by
doing this

Put this formula somewhere off the screen in row4

SUMPRODUCT((MOD(COLUMN(G4:K4),3)0)*(G4:K40))

copy down to and including row 10 (so if you put it in Z4 then copy it down
to Z10)

then just use this formula

=SUMPRODUCT(--(D4:D10="DS"),--(Z4:Z100))

which will return 2

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
That does help, but I need to only show 2. If multiple exceptions appear
on
the same row I still only want to count it as 1 to make it calculate
2/2=100%. Is there another conditional statement I could add?
--
Deb


"Peo Sjoblom" wrote:

You certainly did, I somehow missed the J10. Try this

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


I missed the second 0 in my original formula, this shows 3 now

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
Thanks for all your help on this.

Yes. I think I had stated earlier that I had a 2 in J10.
--
Deb


"Peo Sjoblom" wrote:

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

COUNTIF help
 

I was hoping there would be something fairly simple.

What is this actually doing? I am not familiar with the "MOD" and what is
the 3 doing?

SUMPRODUCT((MOD(COLUMN(G4:K4),3)0)*(G4:K40))

So is there no way to say if "DS" and the range is 1=1?

--
Deb


"Peo Sjoblom" wrote:

There might be something fairly complicated, I'll post back when I have
played a round a bit
and if I find something.
However it is not the best of spreadsheet designs. You could easily solve by
doing this

Put this formula somewhere off the screen in row4

SUMPRODUCT((MOD(COLUMN(G4:K4),3)0)*(G4:K40))

copy down to and including row 10 (so if you put it in Z4 then copy it down
to Z10)

then just use this formula

=SUMPRODUCT(--(D4:D10="DS"),--(Z4:Z100))

which will return 2

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
That does help, but I need to only show 2. If multiple exceptions appear
on
the same row I still only want to count it as 1 to make it calculate
2/2=100%. Is there another conditional statement I could add?
--
Deb


"Peo Sjoblom" wrote:

You certainly did, I somehow missed the J10. Try this

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


I missed the second 0 in my original formula, this shows 3 now

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
Thanks for all your help on this.

Yes. I think I had stated earlier that I had a 2 in J10.
--
Deb


"Peo Sjoblom" wrote:

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

COUNTIF help
 

It jumps over column I, if you only have text in I and no numbers you can
use

=COUNTIF(G4:K4,"0")

and copy down to row 10, what it will do is to count the values in these
rows and if there are 4 values 0 in G4, H4, J4 and K4 it will return 4 but
used
in this formula

=SUMPRODUCT(--(D4:D10="DS"),--(Z4:Z100))

(I assume you put the formula in Z4 and copied down to Z10)

it will only count that as one occasion and not 4 if D4 contains "DS"





--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
I was hoping there would be something fairly simple.

What is this actually doing? I am not familiar with the "MOD" and what is
the 3 doing?

SUMPRODUCT((MOD(COLUMN(G4:K4),3)0)*(G4:K40))

So is there no way to say if "DS" and the range is 1=1?

--
Deb


"Peo Sjoblom" wrote:

There might be something fairly complicated, I'll post back when I have
played a round a bit
and if I find something.
However it is not the best of spreadsheet designs. You could easily solve
by
doing this

Put this formula somewhere off the screen in row4

SUMPRODUCT((MOD(COLUMN(G4:K4),3)0)*(G4:K40))

copy down to and including row 10 (so if you put it in Z4 then copy it
down
to Z10)

then just use this formula

=SUMPRODUCT(--(D4:D10="DS"),--(Z4:Z100))

which will return 2

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
That does help, but I need to only show 2. If multiple exceptions
appear
on
the same row I still only want to count it as 1 to make it calculate
2/2=100%. Is there another conditional statement I could add?
--
Deb


"Peo Sjoblom" wrote:

You certainly did, I somehow missed the J10. Try this

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


I missed the second 0 in my original formula, this shows 3 now

--


Regards,


Peo Sjoblom

"Deb" wrote in message
...
Thanks for all your help on this.

Yes. I think I had stated earlier that I had a 2 in J10.
--
Deb


"Peo Sjoblom" wrote:

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