Sumproduce nested with Countif

[mkalawa]

I am so close but yet so far away.... ok-

A B C D E F
[TABLE="width: 958"]
<colgroup><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]20141219-DE-IT-0001
[/TD]
[TD]tng
[/TD]
[TD]greece
[/TD]
[TD]bread
[/TD]
[TD]man
[/TD]
[TD]19.11.2014[/TD]
[/TR]
[TR]
[TD]20141217-DE-FB-0001[/TD]
[TD]tng
[/TD]
[TD]greece[/TD]
[TD]bread[/TD]
[TD]woman
[/TD]
[TD]16.12.2014[/TD]
[/TR]
[TR]
[TD]20141219-DE-FB-0002[/TD]
[TD]tng
[/TD]
[TD]greece
[/TD]
[TD]bread
[/TD]
[TD]boy
[/TD]
[TD]19.12.2014[/TD]
[/TR]
[TR]
[TD]20150319-VDB-RRR-FB-0002[/TD]
[TD]tng
[/TD]
[TD]greeced
[/TD]
[TD]bread
[/TD]
[TD]girl
[/TD]
[TD]23.03.2015[/TD]
[/TR]
</tbody>[/TABLE]
.
.
.

SUMPRODUCT(--(IKS_Zentral!$F$9:$F$1000<>"");--(MONTH(IKS_Zentral!$F$9:$F$1000)=11);--(YEAR(IKS_Zentral!$F$9:$F$1000)=2014))

the above formular produces this result:

[TABLE="width: 236"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]November, 2014[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]December, 2014[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]January, 2015[/TD]
[TD="align: right"]0
[/TD]
[/TR]
[TR]
[TD]February, 2015[/TD]
[TD="align: right"]0
[/TD]
[/TR]
</tbody>[/TABLE]
...

However, I want to select from a range (IKS_Zentral!$A$9:$F$1000) where each cell contains a specific string. meaning i only want to sum a range A:A that contain a specific string.... (IT),

I wrote the below formular but I am receiving a #VALUE error message: =SUMPRODUCT(COUNTIF(IKS_Zentral!A9:A1000;"*IT*");--(IKS_Zentral!$F$9:$F$1000<>"");--(MONTH(IKS_Zentral!$F$9:$F$1000)=11);--(YEAR(IKS_Zentral!$F$9:$F$1000)=2014))

Your expertise is highly appreciated.

 

[123rickfear]

=COUNTIFS(H:H,2014,G:G,11,A:A,"*IT*")

in column G I did =month(F:F) and in H =year(F:F)

 

[Jonmo1]

Without the helper columns to extract month and year..

=COUNTIFS(F:F,">="&DATE(2014,11,1),F:F,"<="&DATE(2014,11,30),A:A,"*IT*")

 

[mkalawa]

Without the helper columns to extract month and year..

=COUNTIFS(F:F,">="&DATE(2014,11,1),F:F,"<="&DATE(2014,11,30),A:A,"*IT*")

Jonmo1 thanks a lot, very clean... appreciate the support.

 

[Jonmo1]

You're welcome.