Count If in Access 2016?

[britsgal]

Good Afternoon,

I know I can perform a "countif" in Excel but just wondering how I can do that in Access? I really don't want to use code just simple formula would work if that's possible. Here's what I want to do, I have a table containing location, item and on hand. I would like to count the # of zeroes or qty's less than zero by location. The table looks something like this. So in this case location "A" would have 3, Location "B" would have 1, and location "C" would have 3. Any help is appreciated.

LOCATION	ITEM	On Hand
A	1	0
A	2	-3
A	3	4
A	4	-1
A	5	9
A	6	6
B	6	0
B	4	6
C	3	0
C	2	0
C	1	0
C	4	9

<tbody>
</tbody><colgroup><col><col span="2"></colgroup>

 

[alansidman]

I would do this with two queries.

Query1

SELECT Sheet1.LOCATION, IIf([On Hand]<1,1,0) AS Cnt
FROM Sheet1;

Query2

SELECT Query1.LOCATION, Sum(Query1.Cnt) AS SumOfCnt
FROM Query1
GROUP BY Query1.LOCATION;

 

[britsgal]

Thank you! I'll try that!!!

 

[Joe4]

Its actually pretty easy to do it one query also (you can aggregate on a calculated field), i.e.

SELECT LOCATION, Sum(IIf([On Hand]<1,1,0)) AS Cnt
FROM Sheet1
GROUP BY LOCATION;

 

[Kyle123]

Wouldn't

select Location, count(*) from Sheet1
where [on hand] < 0
group by Location

Be simpler?

 

[james_lankford]

Wouldn't

select Location, count(*) from Sheet1
where [on hand] < 0
group by Location

Be simpler?

was just going to post that

 

[Micron]

< 0

Aren't zeros wanted?

 

[Kyle123]

<= 0 then