counting record based on month

[aamir]

i have big sheet with lot of data and in column B there is date dd-mm-yy.

How can i count specific product (column A) based on date (column B) for each month) on sheet B.

 

[VoG]

Try like this

=SUMPRODUCT(--(MONTH(B1:B100)=10),--(A1:A100="X"))

 

[Andrew Poulsom]

Example:

=SUMPRODUCT(--(Sheet1!A1:A1000=A1),--(MONTH(Sheet1!B1:B1000)=MONTH(B1)))

 

[aamir]

Example:

=SUMPRODUCT(--(Sheet1!A1:A1000=A1),--(MONTH(Sheet1!B1:B1000)=MONTH(B1)))

thanks thts working ..

 

[aamir]

i am using this formula

=SUMPRODUCT([COLOR=Red]--(Data!$G$2:$G$500&"<"&98%)[/COLOR],--(Data!$A$2:$A$20000=$A3),--(Data!$I$2:$I$20000=$R$2),--(MONTH(Data!$B$2:$B$20000)=MONTH($F$1)))

problem is in highlighted part, wht i want to do is if the value is less than 98% of cell value then sum otherwise don't consider this value. simple isn't it

 

[VoG]

Perhaps

=SUMPRODUCT(--(Data!$G$2:$G$500 < 0.98),--(Data!$A$2:$A$20000=$A3),--(Data!$I$2:$I$20000=$R$2),--(MONTH(Data!$B$2:$B$20000)=MONTH($F$1)))

 

[aamir]

its giving me #value error, btw the value in my Data G:G cell is % format.

 

[VoG]

All the ranges must be the same size. Try

=SUMPRODUCT(--(Data!$G$2:$G$20000 < 0.98),--(Data!$A$2:$A$20000=$A3),--(Data!$I$2:$I$20000=$R$2),--(MONTH(Data!$B$2:$B$20000)=MONTH($F$1)))