# Countifs, Sumifs, Dynamic Arrays

#### Firstartemis

##### New Member
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)
Code:
`` =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))``
Code:
`` =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)``
Code:
``  =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.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Firstartemis

##### New Member
Figured out both formulas (listed below) minor hiccup. Countifs 2 criteria, multiple columns
Code:
`` =SUMPRODUCT((D1:D6="TYCOM or CMD Specific")*(E1:J6>0)*(C1:C6="Other"))``
Sumifs 2 critera, multiple columns
Code:
`` =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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,012
Messages
5,834,923
Members
430,326
Latest member
tomwax46

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back