need a count for two conditions

[xrayservice]

Example:

First condition, A1:A20 if = B1
Second condition A1:A20 if > B2

Count only ones = to B1 and greater the B2

Tried
COUNTIF(A1:A20,B1) + (A1:A20>B2)
SUM(IF((A1:A20,B1) + (A1:A20>B2),1,0)

Any help, thanks

 

[VoG]

Try

=COUNTIF(A1:A20,B1) + COUNTIF(A1:A20,">"&B2)

 

[xrayservice]

Its closer but didn't work. The statement counted all of conditions 2 even if they were not equal to condition 1

 

[VoG]

If both conditions must be satisfied try

=SUMPRODUCT(--(A1:A20=B1),--(A1:A20>B2))

 

[xrayservice]

Came up with a count of 0

<TABLE dir=ltr border=1 cellSpacing=0 borderColor=#000000 cellPadding=2 width=869><TBODY><TR><TD height=7 width="6%">

Date Completed​

​

​

​

​

</TD><TD height=7 width="8%">

Reference​

​

​

​

​

</TD><TD height=7 width="6%">

Tester​

​

​

​

​

</TD><TD height=7 width="16%">

Action Plan​

​

​

​

​

</TD><TD height=7 width="26%">
</TD><TD height=7 width="8%">

Procedures​

​

​

​

​

</TD><TD height=7 width="7%">

Late​

​

​

​

​

</TD><TD height=7 width="9%">

Vendors​

​

​

​

​

</TD><TD height=7 width="8%">

Count​

​

​

​

​

</TD><TD height=7 width="6%">

Count Late​

​

​

​

​

</TD></TR><TR><TD bgColor=#ccffcc height=7 width="6%">

2/24/2011​

​

​

​

​

</TD><TD height=7 width="8%">

NYSDOH​

​

​

​

Guide to QA​

</TD><TD height=7 width="6%">

In-House​

​

​

​

​

</TD><TD height=7 width="16%">

None​

​

​

​

​

</TD><TD height=7 width="26%">
</TD><TD height=7 width="8%">

TRUE​

​

​

​

​

</TD><TD height=7 width="7%">

0​

​

​

​

​

</TD><TD height=7 width="9%">

In-House​

​

​

​

​

</TD><TD height=7 width="8%">

7​

​

​

​

​

</TD><TD height=7 width="6%">

0​

​

​

​

​

</TD></TR><TR><TD bgColor=#ccffcc height=7 width="6%">

2/22/2011​

​

​

​

​

</TD><TD height=7 width="8%">

NYSDOH​

​

​

​

Guide to QA​

</TD><TD height=7 width="6%">

In-House​

​

​

​

​

</TD><TD height=7 width="16%">

None​

​

​

​

​

</TD><TD height=7 width="26%">
</TD><TD height=7 width="8%">

TRUE​

​

​

​

​

</TD><TD height=7 width="7%">

0​

​

​

​

​

</TD><TD height=7 width="9%">

GE​

​

​

​

​

</TD><TD height=7 width="8%">

0​

​

​

​

​

</TD><TD height=7 width="6%">
</TD></TR><TR><TD bgColor=#ccffcc height=7 width="6%">

2/24/2011​

​

​

​

​

</TD><TD height=7 width="8%">

NYSDOH​

​

​

​

Guide to QA​

</TD><TD height=7 width="6%">

In-House​

​

​

​

​

</TD><TD height=7 width="16%">

None​

​

​

​

​

</TD><TD height=7 width="26%">
</TD><TD height=7 width="8%">

TRUE​

​

​

​

​

</TD><TD height=7 width="7%">

0​

​

​

​

​

</TD><TD height=7 width="9%">

Philips​

​

​

​

​

</TD><TD height=7 width="8%">

0​

​

​

​

​

</TD><TD height=7 width="6%">
</TD></TR><TR><TD bgColor=#ccffcc height=7 width="6%">

2/23/2011​

​

​

​

​

</TD><TD height=7 width="8%">

NYSDOH​

​

​

​

Guide to QA​

</TD><TD height=7 width="6%">

In-House​

​

​

​

​

</TD><TD height=7 width="16%">

None​

​

​

​

​

</TD><TD height=7 width="26%">
</TD><TD height=7 width="8%">

TRUE​

​

​

​

​

</TD><TD height=7 width="7%">

0​

​

​

​

​

</TD><TD height=7 width="9%">

Toshiba​

​

​

​

​

</TD><TD height=7 width="8%">

0​

​

​

​

​

</TD><TD height=7 width="6%">
</TD></TR><TR><TD bgColor=#ccffcc height=7 width="6%">

2/25/2011​

​

​

​

​

</TD><TD height=7 width="8%">

NYSDOH​

​

​

​

Guide to QA​

</TD><TD height=7 width="6%">

In-House​

​

​

​

​

</TD><TD height=7 width="16%">

None​

​

​

​

​

</TD><TD height=7 width="26%">
</TD><TD height=7 width="8%">

TRUE​

​

​

​

​

</TD><TD height=7 width="7%">

0​

​

​

​

​

</TD><TD height=7 width="9%">

Siemens​

​

​

​

​

</TD><TD height=7 width="8%">

0​

​

​

​

​

</TD><TD height=7 width="6%">
</TD></TR><TR><TD bgColor=#ccffcc height=7 width="6%">

2/15/2011​

​

​

​

​

</TD><TD height=7 width="8%">

NYSDOH​

​

​

​

Guide to QA​

</TD><TD height=7 width="6%">

In-House​

​

​

​

​

</TD><TD height=7 width="16%">

None​

​

​

​

​

</TD><TD height=7 width="26%">
</TD><TD height=7 width="8%">

TRUE​

​

​

​

​

</TD><TD height=7 width="7%">

0​

​

​

​

​

</TD><TD height=7 width="9%">

Other​

​

​

​

​

</TD><TD height=7 width="8%">

1​

​

​

​

​

</TD><TD height=7 width="6%">
</TD></TR><TR><TD bgColor=#ccffcc height=7 width="6%">

2/28/2011​

​

​

​

​

</TD><TD height=7 width="8%">

NYSDOH​

​

​

​

Guide to QA​

</TD><TD height=7 width="6%">

In-House​

​

​

​

​

</TD><TD height=7 width="16%">

None​

​

​

​

​

</TD><TD height=7 width="26%">
</TD><TD height=7 width="8%">

TRUE​

​

​

​

​

</TD><TD height=7 width="7%">

0​

​

​

​

​

</TD><TD height=7 width="9%">

0​

​

​

​

​

</TD><TD height=7 width="8%">

0​

​

​

​

​

</TD><TD height=7 width="6%">
</TD></TR><TR><TD bgColor=#ffff99 height=7 width="6%">

3/3/2011​

​

​

​

​

</TD><TD height=7 width="8%">

NYSDOH​

​

​

​

Guide to QA​

</TD><TD height=7 width="6%">

Other​

​

​

​

​

</TD><TD height=7 width="16%">

None​

​

​

​

​

</TD><TD height=7 width="26%">
</TD><TD height=7 width="8%">

TRUE​

​

​

​

​

</TD><TD height=7 width="7%">

1​

​

​

​

​

</TD><TD height=7 width="9%">

0​

​

​

​

​

</TD><TD height=7 width="8%">

0​

​

​

​

​

</TD><TD height=7 width="6%">
</TD></TR></TBODY></TABLE>

Answer I'm looking for is how many late for each vendor, in this example there is one and its Other

 

[VoG]

Sorry but I cannot see how A1:A20 and B1 and B2 make any sense in relation to that exhibit.

Also, that won't paste properly into Excel. Try using Excel Jeanie to post a shot of your sheet and describe in words what you want to calculate.

Link http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[xrayservice]

Let me explain again I wasn't clear with the information I provided you


<TABLE dir=ltr border=1 cellSpacing=0 borderColor=#000000 cellPadding=2 width=1274><TBODY><TR><TD height=7 width="9%">Equipment

​

</TD><TD height=7 width="8%">Procedure

​

</TD><TD height=7 width="5%">Frequency

​

</TD><TD height=7 width="4%">Date Completed

​

</TD><TD height=7 width="5%">Reference

​

</TD><TD height=7 width="4%">Tester

​

</TD><TD height=7 width="11%">Action Plan

</TD><TD height=7 width="18%">

​

</TD><TD height=7 width="6%">Procedures

​

</TD><TD height=7 width="5%">Late

​

</TD><TD height=7 width="6%">Tester

​

</TD><TD height=7 width="5%">Count

​

</TD><TD height=7 width="4%">Count Late

​

</TD><TD height=7 width="5%">Procedures

</TD><TD height=7 width="4%">

​

</TD></TR><TR><TD height=7 width="9%">Rm# 3

GE Rad/Fluoro​

​

</TD><TD height=7 width="8%">Collimation

​

</TD><TD height=7 width="5%">Quarterly

QAQS

​

</TD><TD bgColor=#ccffcc height=7 width="4%">2/24/2011

​

</TD><TD height=7 width="5%">NYSDOH

Guide to QA

​

</TD><TD height=7 width="4%">In-House

​

</TD><TD height=7 width="11%">None

</TD><TD height=7 width="18%">

​

</TD><TD height=7 width="6%">TRUE

​

</TD><TD height=7 width="5%">0

​

</TD><TD height=7 width="6%">In-House

​

</TD><TD height=7 width="5%">6

​

</TD><TD height=7 width="4%">0

​

</TD><TD height=7 width="5%">Total

​

</TD><TD height=7 width="4%">8

​

</TD></TR><TR><TD height=7 width="9%">IRS Suite# 1

Siemens MultiStar​

​

</TD><TD height=7 width="8%">Collimation, dose, generator, image

​

</TD><TD height=7 width="5%">Annual

AQSQ

​

</TD><TD bgColor=#ccffcc height=7 width="4%">2/22/2011

​

</TD><TD height=7 width="5%">NYSDOH

Guide to QA

​

</TD><TD height=7 width="4%">In-House

​

</TD><TD height=7 width="11%">None

</TD><TD height=7 width="18%">

​

</TD><TD height=7 width="6%">TRUE

​

</TD><TD height=7 width="5%">0

​

</TD><TD height=7 width="6%">GE

​

</TD><TD height=7 width="5%">0

</TD><TD height=7 width="4%">

​

</TD><TD height=7 width="5%">Total

Late​

​

​

​

</TD><TD height=7 width="4%">1

​

</TD></TR><TR><TD height=7 width="9%">IRS Suite# 2

Siemens Artis​

​

</TD><TD height=7 width="8%">Collimation, dose, generator, image

​

</TD><TD height=7 width="5%">Annual

AQSQ

​

</TD><TD bgColor=#ccffcc height=7 width="4%">2/24/2011

​

</TD><TD height=7 width="5%">NYSDOH

Guide to QA

​

</TD><TD height=7 width="4%">In-House

​

</TD><TD height=7 width="11%">None

</TD><TD height=7 width="18%">

​

</TD><TD height=7 width="6%">TRUE

​

</TD><TD height=7 width="5%">0

​

</TD><TD height=7 width="6%">Philips

​

</TD><TD height=7 width="5%">0

</TD><TD height=7 width="4%">

​

</TD><TD height=7 width="5%">Total based on active

​

</TD><TD height=7 width="4%">8

​

</TD></TR><TR><TD height=7 width="9%">Protective Lead Testing

Main X-Ray​

​

</TD><TD height=7 width="8%">Quality Testing

​

</TD><TD height=7 width="5%">Semi

S

S

​

</TD><TD bgColor=#ccffcc height=7 width="4%">2/23/2011

​

</TD><TD height=7 width="5%">NYSDOH

Guide to QA

​

</TD><TD height=7 width="4%">In-House

​

</TD><TD height=7 width="11%">None

</TD><TD height=7 width="18%">

​

</TD><TD height=7 width="6%">TRUE

​

</TD><TD height=7 width="5%">0

​

</TD><TD height=7 width="6%">Toshiba

​

</TD><TD height=7 width="5%">1

</TD><TD height=7 width="4%">

</TD><TD height=7 width="5%">

</TD><TD height=7 width="4%">

​

</TD></TR><TR><TD height=7 width="9%">Protective Lead Testing

IRS - Specials​

​

</TD><TD height=7 width="8%">Quality Testing

​

</TD><TD height=7 width="5%">Semi

S

S

​

</TD><TD bgColor=#ccffcc height=7 width="4%">2/25/2011

​

</TD><TD height=7 width="5%">NYSDOH

Guide to QA

​

</TD><TD height=7 width="4%">Toshiba

​

</TD><TD height=7 width="11%">None

</TD><TD height=7 width="18%">

​

</TD><TD height=7 width="6%">TRUE

​

</TD><TD height=7 width="5%">0

​

</TD><TD height=7 width="6%">Siemens

​

</TD><TD height=7 width="5%">0

</TD><TD height=7 width="4%">

</TD><TD height=7 width="5%">

</TD><TD height=7 width="4%">

​

</TD></TR><TR><TD height=7 width="9%">Protective Lead Testing

Operating Room​

​

</TD><TD height=7 width="8%">Quality Testing

​

</TD><TD height=7 width="5%">Semi

S

S

​

</TD><TD bgColor=#ccffcc height=7 width="4%">2/15/2011

​

</TD><TD height=7 width="5%">NYSDOH

Guide to QA

​

</TD><TD height=7 width="4%">In-House

​

</TD><TD height=7 width="11%">None

</TD><TD height=7 width="18%">

​

</TD><TD height=7 width="6%">TRUE

​

</TD><TD height=7 width="5%">0

​

</TD><TD height=7 width="6%">Other

​

</TD><TD height=7 width="5%">1

</TD><TD height=7 width="4%">

</TD><TD height=7 width="5%">

</TD><TD height=7 width="4%">

​

</TD></TR><TR><TD height=7 width="9%">Protective Lead Testing

Cath lab​

​

</TD><TD height=7 width="8%">Quality Testing

​

</TD><TD height=7 width="5%">Semi

S

S

​

</TD><TD bgColor=#ccffcc height=7 width="4%">2/28/2011

​

</TD><TD height=7 width="5%">NYSDOH

Guide to QA

​

</TD><TD height=7 width="4%">In-House

​

</TD><TD height=7 width="11%">None

</TD><TD height=7 width="18%">

​

</TD><TD height=7 width="6%">TRUE

​

</TD><TD height=7 width="5%">0

​

</TD><TD height=7 width="6%">0

​

</TD><TD height=7 width="5%">0

</TD><TD height=7 width="4%">

</TD><TD height=7 width="5%">

</TD><TD height=7 width="4%">

​

</TD></TR><TR><TD height=7 width="9%">Protective Lead Testing

Dental​

​

</TD><TD height=7 width="8%">Quality Testing

​

</TD><TD height=7 width="5%">Semi

​

​

S

S

​

</TD><TD bgColor=#ffff99 height=7 width="4%">3/3/2011

​

</TD><TD height=7 width="5%">NYSDOH

Guide to QA

​

</TD><TD height=7 width="4%">Other

​

</TD><TD height=7 width="11%">None

</TD><TD height=7 width="18%">

​

</TD><TD height=7 width="6%">TRUE

​

</TD><TD height=7 width="5%">1

​

</TD><TD height=7 width="6%">0

​

</TD><TD height=7 width="5%">0

</TD><TD height=7 width="4%">

</TD><TD height=7 width="5%">

</TD><TD height=7 width="4%">

</TD></TR></TBODY></TABLE>
​

It counts if text is in a Procedure box as 1
It counts late dates
I need to combine a late date with the the tester as a count
Example Other counts 1, In-House counts 0, Toshiba count 0​

 

[VoG]

Maybe

=SUMPRODUCT(--(F1:F20="Other"),--(J1:J20=1))

 

[xrayservice]

I figured it out!

{=SUM(IF(I4:I27=N4,IF(G4:G27>N26,1,0)))}

It had to be entered as an array