Countifs, Sumifs, Dynamic Arrays

[Firstartemis]

There is a formula that will do this, but I can't figure it out. I've spent the last 3 hours trying to figure it out to no avail. Here's my situation: Boss- Extremely computer illiterate. She can't figure out how to turn on macros, or sync with sharepoint so anything vba related is out of the question. We use Sharepoint to track events and attendance at work. It tracks raw data, which she just has to select the month and examine the numbers. However, it does not populate her report (she cant figure out that she needs to click options...). Additionally, she changes her mind on the categories of these events and expects me to figure out how to fix the tracker every other week. It's extremely frustrating. So this is what I have now:

Event	Category	Subcategory	1 Jan 2015	...	31 Dec 2015
SafeTALK	Other	Other TYCOM or CMD Specific	25	0	30

I need a formula to count all events during a given month that matches category and subcategory. I also need to add the attendance. (in the table example, 2 events, 55 participants). Some things I've tried: A15 is the subcategory on the report. A2 is the 1st day of the month to filter the results (MMMM format, list of all months)

 =SUMPRODUCT(COUNTIF('Preventive Actions'!D:D,"="&A15)*--(INDIRECT(ADDRESS(2,MATCH(A2,'Preventive Actions'!1:1,0),1,1,"Preventive Actions")&":"&ADDRESS(COUNTA('Preventive Actions'!A:A),MATCH(DATE(YEAR(A2),MONTH(A2)+1,DAY(1))-1,'Preventive Actions'!1:1,0),1,1,))>0))

 =COUNTIFS(INDIRECT(ADDRESS(2,MATCH(A2,'Preventive Actions'!1:1,0),1,1,"Preventive Actions")&":"&ADDRESS(COUNTA('Preventive Actions'!A:A),MATCH(DATE(YEAR(A2),MONTH(A2)+1,DAY(1))-1,'Preventive Actions'!1:1,0),1,1,)),"]0",'Preventive Actions'!C:C,"="&A14)

  =SUMPRODUCT(--(D2:D6="TYCOM or CMD Specific"),--(C2:C6="Other")*E2:J6)  I'm missing something, and I just can't link them together.

Thank you for any assistance you may provide.

 

[Firstartemis]

Figured out both formulas (listed below) minor hiccup. Countifs 2 criteria, multiple columns

 =SUMPRODUCT((D1:D6="TYCOM or CMD Specific")*(E1:J6>0)*(C1:C6="Other"))

Sumifs 2 critera, multiple columns

 =SUMPRODUCT((D1:D6="TYCOM or CMD Specific")*(E1:J6)*(C1:C6="Other"))

My second row of data uses "x" as a place holder. SumProduct cannot renders an error! with any non number value regardless of format. I can get around this with using a 1, but is there a way to do this with a formula? I'm trying to make this as dummy proof as possible. Thank you.