Countifs, Sumifs, Dynamic Arrays

Firstartemis

New Member
Joined
Jun 17, 2015
Messages
10
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:
EventCategorySubcategory1 Jan 2015...31 Dec 2015
SafeTALKOtherOther TYCOM or CMD Specific25030
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.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Firstartemis

New Member
Joined
Jun 17, 2015
Messages
10
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,516
Messages
5,625,264
Members
416,086
Latest member
CaptainGD

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top