=SUMIF and =COUNTIF in the same formula

[DPChristman]

I am looking to create a formula using both the sumf and countif operators.

Example, Column A contains store numbers. Column D contains deposit variances(positive and negative numbers).

However, there is an entry for every store, every day, even if there is no variance

So, what I am try to accomplish is the following:

If the store number is 1, how many times how many times is the variance <>0

The not working version of this formula would be something like =SUMIF(A:A,A2,countif(D:D<>0))

I know that that doesn't work.

To further complicate the formula, we have a tab for each month of the year, so for February's entry, it might look something like this (again, this doesn't work):

=SUMIF('FEB 2016'!A:A,A2,"countif('FEB 2016'!D:D<>))

Is there an easy way to do this?

 

[63falcondude]

How about this?

Excel 2010
	A	B	C	D	E
1	Store Number	Deposit Variances		Store #	# of times variance not 0
2	1	0		1	2
3	1	1
4	1	-1
5	2	1
6	2	0
Sheet2

Cell Formulas
Range	Formula
E2	=COUNTIFS(A:A,D2,B:B,"<>0")

 

[DPChristman]

Thanks. That worked. I kicked myself as soon as a saw =COUNTIFS .
I know about countifs. I just forgot, I guess.

The adjusted version of the formula that I had to use because of the separate tabs is this =COUNTIFS('FEB 2016'!$A:$A,SUMMARY!$A4,'FEB 2016'!$D:$D,"<>0"), though I probably didn't need to reference the tab (SUMMARY) where the formula resides.

Thanks again!