Excel - Countif

[Tmt]

I'm trying to use this formular to pull the count of items from another sheet
under 2 conditions: belongs to a department & being serviced. The sequence of
"F2+Ctrl+Shift+Enter" would not get it to work, but it seems to work on the
local sheet that carries the data, curiously, minus 1 count!
=COUNT(IF(('Q3'!D12194,"=calibrated")*('Q3'!G12:G194,"=70223"),'Q3'!H12:H194))

Here's part of the sheet that I'm confused. Please help. Thanks.
M702A calibrated 70222B $100.00
T-berd 310 calibrated 70222D $300.00
MP1570A OFR 8/6/2009 70222E $0.00
156 calibrated 70222E $364.59
T-Berd 310 calibrated 70223 $300.00
T-Berd 310 calibrated 70223 $300.00
T-Berd 310 ACTIVE 8/6/2009 70223 $0.00
T-Berd 310 calibrated 70223 $300.00
T-Berd 310 calibrated 70223 $300.00

 

[Pete_UK]

I think you need to do this:

=SUMPRODUCT(('Q3'!D12194="calibrated")*('Q3'!G12:G194="70223"))

to get a count of the number that satisfy both conditions. No need for
Ctrl-Shift-Enter.

Hope this helps.

Pete

 

[Tmt]

Thanks for your help. It solves the problem.

Now that if I tried to pull the 70222 with an * in the formular (to get all
the /A/B/D/E in the datasheet), then the result is 0? Any solution for that?

tom

 

[Pete_UK]

Yes, try it this way:

=SUMPRODUCT(('Q3'!D12194="calibrated")*(LEFT('Q3'!G12:G194,5)
="70222"))

You can't use an asterisk as a wilcard in this situation.

Hope this helps.

Pete