COUNTIF help

[Deb]

=COUNTIFS(G$4:H$10,">0",D$4$10,"CR")+COUNTIFS(J$4:K$10,">0",D$4$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.

 

[Peo Sjoblom]

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((D410="CR")*(MOD(COLUMN(G4:K10),3)>0)*(G4:K10>0))


or


=SUMPRODUCT((D413="CR")*(G4:H13>0))+SUMPRODUCT((D413="CR")*(J4:K13>0))

--


Regards,


Peo Sjoblom

 

[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$10>0))+SUMPRODUCT(($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.

 

[Peo Sjoblom]

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$10>0))+SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($J$4:$K$10))

--


Regards,


Peo Sjoblom

 

[Deb]

My spreadsheet is showing "4".
--
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$10>0))+SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($J$4:$K$10))

--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

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

--


Regards,


Peo Sjoblom

 

[Deb]

Thanks for all your help on this.

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

 

[Peo Sjoblom]

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

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


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

--


Regards,


Peo Sjoblom

 

[Deb]

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?

 

[Peo Sjoblom]

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:K4>0))

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(--(D410="DS"),--(Z4:Z10>0))

which will return 2

--


Regards,


Peo Sjoblom

 

[Deb]

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:K4>0))

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

 

[Peo Sjoblom]

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(--(D410="DS"),--(Z4:Z10>0))

(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