Sumproduct formula

Hi Experts,

I would like to request Experts help create a Sumproduct formula for "Table1" and Table2" at "Report" sheet based on data at "Data" sheet.

Table1: need to count how many time the same date are appeared at Column E (Data_sheet)  according to month.

Table2: Count how many time the listed "Item" at Column C (Data_sheet) appeared based on Month (Column E _Data sheet)  

I have manually inserted a few data at Table 1 and 2 for Experts to get better view. Hope Experts could help.
 
CountData.xls
CartilloAsked:
Who is Participating?
 
gowflowConnect With a Mentor Commented:
This is for Table1
am working on Table2 !!!
gowflow
CountData.xls
0
 
CartilloAuthor Commented:
Hi gowflow,

Thanks for the formula.
0
 
SANTABABYConnect With a Mentor Commented:
Table1:
Use the following formula in B3 and then copy the content of the cell(B3) to other similar cells in table1.
=COUNTIF(Data!$E:$E,A3)

Table2:
Use the following formula in M3 and then copy the content of the cell(M3) to other similar cells in table2.
=COUNTIFS(Data!$C:$C,"="&$L3,Data!$E:$E,">="&DATE(YEAR(INDIRECT(ADDRESS(2,COLUMN(),1))),MONTH(INDIRECT(ADDRESS(2,COLUMN(),1))),1),Data!$E:$E,"<"&DATE(YEAR(INDIRECT(ADDRESS(2,COLUMN(),1))),MONTH(INDIRECT(ADDRESS(2,COLUMN(),1)))+1,1))


It appears a little complicated. I'll think about simplifying it further. Please let me know if it works.

Thanks.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
barry houdiniConnect With a Mentor Commented:
You could use this formula for table 2 - in M3 copied across and down

=SUMPRODUCT((Data!$C$2:$C$1000=$L3)*(TEXT(Data!$E$2:$E$1000,"mmm-yy")=TEXT(M$2,"mmm-yy")))

format as 0;; to hide zeroes, see attached

regards, barry
27303595.xls
0
 
gowflowCommented:
here is both
gowflow
CountData.xls
0
 
CartilloAuthor Commented:
Hi,

Thanks a lot for the formulas.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.