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:
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)
Thank you for any assistance you may provide.
Event | Category | Subcategory | 1 Jan 2015 | ... | 31 Dec 2015 |
SafeTALK | Other | Other TYCOM or CMD Specific | 25 | 0 | 30 |
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.