Hello,
I hope someone can help me out!
I am trying to do three separate formulas under Total Transactions, Meet, and Average working days
1. Total Transactions:
A) So for Total Transactions I want to know how many times the word "cas" is in a certain column, BUT only if it is in a certain month (the months are in another column Q5:Q54 and formatted like this 1-Jun-2015)
So I did this =COUNTIF([Files.xlsx]Template!$H$5:$H$54,"cas") and it works, but I need to only pull "cas" for each month, so one cell would populate cas for june, another would be cas for july etc. How do I do that?
B) Also same thing but in this case I have 3 different words in a column. So I have this:
=SUMPRODUCT(--ISNUMBER(MATCH([Files.xlsx]Template!$H$5:$H$54,{"FSWEP","COOP","RAP"},0)))
but I dont know how to separate it by month like above.
2. Meet
A) This has three columns, one would be a specific word in a column, if it is equal to or greater than 1 but less than or equal to 7 in another column, and then by month like above.
So i have:
=COUNTIFS([Files.xlsx]Template!$H$5:$H$54,"cas",[Files.xlsx]Template!$AA$5:$AA$54,"<=7",[Files.xlsx]Template!$AA$5:$AA$54,">=1")
Which finds words that start with "cas" according to a range between 1-7 in another column, BUT i need it by month as well like above.
B) Also same as 1B having three words. I have this but it doesnt count by month
=COUNTIFS([Files.xlsx]Template!$H$5:$H$54,"FSWEP",[Files.xlsx]Template!$AA$5:$AA$54,"<=7",[Files.xlsx]Template!$AA$5:$AA$54,">=1")+COUNTIFS([Files.xlsx]Template!$H$5:$H$54,"COOP",[Files.xlsx]Template!$AA$5:$AA$54,"<=7",[Files.xlsx]Template!$AA$5:$AA$54,">=1")+COUNTIFS([Files.xlsx]Template!$H$5:$H$54,"RAP",[Files.xlsx]Template!$AA$5:$AA$54,"<=7",[Files.xlsx]Template!$AA$5:$AA$54,">=1")
3. Average working days
A) I need to take the column that has the words "cas" and average the number of days in another column, BUT i need to do it by month once again.
So I have this:
=AVERAGEIF([Files.xlsx]Template!$H$5:$H$54,"cas",[Files.xlsx]Template!$AA$5:$AA$54)
B)Again, same thing but need the date for multiple words and have this so far which works:
=AVERAGE(IF(([Files.xlsx]Template!$H$5:$H$54={"FSWEP","COOP","RAP"}),[Files.xlsx]Template!$AA$5:$AA$54))
THANKS!
I hope someone can help me out!
I am trying to do three separate formulas under Total Transactions, Meet, and Average working days
1. Total Transactions:
A) So for Total Transactions I want to know how many times the word "cas" is in a certain column, BUT only if it is in a certain month (the months are in another column Q5:Q54 and formatted like this 1-Jun-2015)
So I did this =COUNTIF([Files.xlsx]Template!$H$5:$H$54,"cas") and it works, but I need to only pull "cas" for each month, so one cell would populate cas for june, another would be cas for july etc. How do I do that?
B) Also same thing but in this case I have 3 different words in a column. So I have this:
=SUMPRODUCT(--ISNUMBER(MATCH([Files.xlsx]Template!$H$5:$H$54,{"FSWEP","COOP","RAP"},0)))
but I dont know how to separate it by month like above.
2. Meet
A) This has three columns, one would be a specific word in a column, if it is equal to or greater than 1 but less than or equal to 7 in another column, and then by month like above.
So i have:
=COUNTIFS([Files.xlsx]Template!$H$5:$H$54,"cas",[Files.xlsx]Template!$AA$5:$AA$54,"<=7",[Files.xlsx]Template!$AA$5:$AA$54,">=1")
Which finds words that start with "cas" according to a range between 1-7 in another column, BUT i need it by month as well like above.
B) Also same as 1B having three words. I have this but it doesnt count by month
=COUNTIFS([Files.xlsx]Template!$H$5:$H$54,"FSWEP",[Files.xlsx]Template!$AA$5:$AA$54,"<=7",[Files.xlsx]Template!$AA$5:$AA$54,">=1")+COUNTIFS([Files.xlsx]Template!$H$5:$H$54,"COOP",[Files.xlsx]Template!$AA$5:$AA$54,"<=7",[Files.xlsx]Template!$AA$5:$AA$54,">=1")+COUNTIFS([Files.xlsx]Template!$H$5:$H$54,"RAP",[Files.xlsx]Template!$AA$5:$AA$54,"<=7",[Files.xlsx]Template!$AA$5:$AA$54,">=1")
3. Average working days
A) I need to take the column that has the words "cas" and average the number of days in another column, BUT i need to do it by month once again.
So I have this:
=AVERAGEIF([Files.xlsx]Template!$H$5:$H$54,"cas",[Files.xlsx]Template!$AA$5:$AA$54)
B)Again, same thing but need the date for multiple words and have this so far which works:
=AVERAGE(IF(([Files.xlsx]Template!$H$5:$H$54={"FSWEP","COOP","RAP"}),[Files.xlsx]Template!$AA$5:$AA$54))
THANKS!
