COUNTIFS Formula

[spuri78]

Hi all,

Please have a look at the spreadsheet in the link below.

https://www.dropbox.com/s/m66qbkduup...Data.xlsm?dl=0

I need help with the following:

Sheet: Region Report

- D11 has formula =COUNTIF('Active Clients '!F:F,"I/Commenced")
I also want to include in the formula records from Non-Active Clients sheet that has SAT Action Status "I/Commenced" from date 2/2/14 onwards.
- D12 has formula =COUNTIFS('Non - Active Clients'!F:F,"I/Completed",'Non - Active Clients'!A:A,">2/2/14")
I want to include in the formula records from Active Clients sheet that has SAT Action Status "I/Completed".

Rest I can do it myself.

Many thanks

[p24leclerc]

there is probably something I don't understand. As I see this, you only have to ADD your 2 formulas in one cell either D11 or D12 and you got it. Both cells will give you the same result though.

Please Login or Register  to view this content.

[spuri78]

Thank you for your response. I'll give you a bit of a brief background on how users use this worksheet.

Active Client Sheet - This is where staff enter active client details.

Non-Active Client Sheet - This is where staff cut and paste client records (that are no longer working with the team) from active to non-active sheet. However, sometimes staff might forget to copy exited clients to the Non-Active sheet. So, in cell D11 for example, I want the formula that will include records from both Active and Non-Active (2/2/14 onwards) that have status "I/Commenced" from Col F to be counted as well. In this worksheet, there isn't any records because staff in this particular team are very efficient. But, I just want to safeguard myself for the purpose of accurate reporting.

Does it make sense?

[spuri78]

Ok. ignore the previous worksheet. Have a look at the one attached.


As you can see in cell B11, the data is coming from records in the Active Sheet that have status "I/Commenced". However, there are also some records (which shouldn't be) in Non-Active Sheet that have status "I/Commenced". So, the data should say 42. Same will go for B12 to B20 i.e. data from both Active and Non-Active Sheet.

Hope it's a bit clearer now.

Thanks

[p24leclerc]

here is the formula you need in B11

Please Login or Register  to view this content.

[spuri78]

Thanks for the formula. Yep, that worked. Another question, have a look at the spreadsheet attached.

In Col K in Active and Non-Active sheet, I have a data validation with formula =K2>=A2. When a user enters an exit date earlier than an entry date, message will pop up. However, I'mseeing that validation being bypassed somehow by users. In the attached sheet, you'd see few exit dates that were before entry dates entered. How is that possible? As mentioned earlier, users copy and paste records from active to non-active sheet. Could that be causing it?

If I could have a fool proof formula so that it doesn't get overwritten, that'd be great.

Thanks

[vlady]

Are you applying filters?

countif/countifs will count hidden rows.
try to hide a row in Active Clients that will give you 17 counts for I/Commenced

Please Login or Register  to view this content.

Please Login or Register  to view this content.

-> the red one could be a cell reference for the date


combine the two (sum)

Please Login or Register  to view this content.

[spuri78]

Thank you for taking time to reply but I'm a bit confused. Yes, the worksheet has filters. Where am I meant to enter the SUMPRODUCT formulas. Col L in Non-Active is just my workings and is not in the worksheet that users use. I filtered it for the purpose of showing the exit date col K so that you can see that users entered the exit date before entry date inspite of my data validation formula.

[vlady]

Sheet: Region Report

- D11

the last formula in post#7

the first formula in post7 is to show the count on Active Clients (this is the countif counterpart)
and the second in post 7 is for Non - Active Clients with a date criteria (this is the countifs counterpart)

since countif/countifs will count also filtered values/hidden
the option 103 inside the subtotal indicates to not count also manually hidden data

[spuri78]

Ah, I get it. However, let's say a user hides a row with status I/Commenced. When I look at the data in cell D11, it would show 41 (Active and Non-Active) due to a row that was hidden instead of 42 which is the right data. wouldn't it be inaccurate data reporting? pardon my ignorance.

Thanks,

[vlady]

Depends on your requirements.

let's say you printed Active Client's and Non - Active Clients with a hidden row then in the report sheet Region Report say's it has 42. But counting it manually will give you 41.
you have to explain that a row is hidden and why it is hidden since someone could say that it is not accurate report.

if it is just a summary report ..hidden or not ..the countif/countifs will be ok.

[spuri78]

hmm, something to think about. I doubt that a user will hide a row. Yep, it is just a summary report. Thanks heaps.

Any idea about my post #6?

[vlady]

another point..
if you like a report for only year 2015 you filter it right then create a report based on that year only. the countif/countifs will count also the 2014 w/c is now incorrect report.

[spuri78]

good point. Since you mentioned, from the next reporting period onwards, data in cell B11 to B20 will be based on the reporting month i.e. 1/1/15 to 31/1/15. How will the formula in D11 look like then as the date range for the data would be based on the reporting month instead of 2/2/14? Do you think that would cause a pain as every month I would have to change the date based on the reporting month in the formula? sample spreadsheet (December data as an example) of how it will look like attached.

[vlady]

lets look at the formula for Non-Active far end formula

from this .....*('Non - Active Clients'!$A$2:$A$1000>2/2/14)*1)

becomes this to check the month and year

....*(TEXT('Non - Active Clients'!$A$2:$A$1000,"mmyyy")=TEXT(H2,"mmyyy"))*1))

cell H2 above holds a sample date where you will get the month and year

say H2 = 1/1/2015

without the year it will be like (all years included)

...*(TEXT('Non - Active Clients'!$A$2:$A$1000,"mmmm")=H2)*1))
H2 then can be the text January

[spuri78]

As I'm using countif, the Non-Active far end formula is this COUNTIFS('Non - Active Clients'!F:F,"I/Commenced",'Non - Active Clients'!A:A,">2/2/14").

Sorry, do you mind having a go again? Use the attached spreadsheet instead.

Thanks

[vlady]

this is the counterpart of countifs since it cannot handle month functions/text

=SUMPRODUCT((('Non - Active Clients'!$F$2:$F$1000=TRIM(LEFT(A11,FIND(" (",A11&" ("))))*(TEXT('Non - Active Clients'!$A$2:$A$1000,"mmmm")=H2)*1))

H2 has the text month like january

or this
=SUMPRODUCT((('Non - Active Clients'!$F$2:$F$1000="I/Commenced")*(TEXT('Non - Active Clients'!$A$2:$A$1000,"mmmm")=H2)*1))



in countifs it would be like this
COUNTIFS('Non - Active Clients'!F:F,"I/Commenced",'Non - Active Clients'!A:A,">="&B1,'Non - Active Clients'!A:A,"<="&B2)

note you have now B1 and B2

B1 now has the first date 1/1/2014
B2 has the ending date 1/31/2014

problem is you have to check the ending date if it false to 30 or 31 days and 28 or 29 daysfor the month of feb

[spuri78]

Thanks heaps. I just realised that I would also have to change the Countif formula which gets the data from Active Client sheet. So, the current formula in cell B11 =COUNTIF('Active Clients '!F:F,"I/Commenced")+COUNTIFS('Non - Active Clients'!F:F,"I/Commenced",'Non - Active Clients'!A:A,">2/2/14") will change to

=SUMPRODUCT((('Active Clients '!$F$2:$F$1000="I/Commenced")*(TEXT('Non - Active Clients'!$A$2:$A$1000,"mmmm")=December)*1))--SUMPRODUCT((('Non - Active Clients'!$F$2:$F$1000="I/Commenced")*(TEXT('Non - Active Clients'!$A$2:$A$1000,"mmmm")=December)*1))

Is that right?

[vlady]

nice!

have to go now.
regards,
vlady

[spuri78]

Thanks heaps for all your help but unfortunately the formula =SUMPRODUCT((('Active Clients '!$F$2:$F$1000="I/Commenced")*(TEXT('Non - Active Clients'!$A$2:$A$1000,"mmmm")=December)*1))--SUMPRODUCT((('Non - Active Clients'!$F$2:$F$1000="I/Commenced")*(TEXT('Non - Active Clients'!$A$2:$A$1000,"mmmm")=December)*1)) is showing #NAME?

Any ideas?

[vlady]

the text December should be enclosed with quotes
"December"

[spuri78]

Thanks Vlady. I did that. The result it's giving is 5 which is incorrect. It should give 25 (18 records in December from Active Sheet and 7 from Non-Active)

[vlady]

sorry did not notice the criteria in first seumproduct

=SUMPRODUCT((('Active Clients '!$F$2:$F$1000="I/Commenced")*(TEXT('Non - Active Clients'!$A$2:$A$1000,"mmmm")="December")*1))--SUMPRODUCT((('Non - Active Clients'!$F$2:$F$1000="I/Commenced")*(TEXT('Non - Active Clients'!$A$2:$A$1000,"mmmm")="December")*1))

that should refer also to sheet Active Clients.

since the first sumprodut refers to Active Clients plus the second sumproduct that refers to N0n - Active Clients

[spuri78]

No worries at all. I changed the formula to =SUMPRODUCT((('Active Clients '!$F$2:$F$1000="I/Commenced")*(TEXT('Active Clients '!$A$2:$A$1000,"mmmm")="December")*1))--SUMPRODUCT((('Non - Active Clients'!$F$2:$F$1000="I/Commenced")*(TEXT('Non - Active Clients'!$A$2:$A$1000,"mmmm")="December")*1)).

Sorry, the result is now 12 which is still not right.

[vlady]

the answer is correct 8 for Active and 4 for Non active total 12

you can test it by applying filter on I/Commenced and again including the month of December only in Column A for the count



Your 18 records is correct IF you will not include the month (dates in column A)

if you will just count the Active Clients for I/Commenced remove the formula *(TEXT('Active Clients '!$A$2:$A$1000,"mmmm")="December")*1 in the first sumproduct same with the

i think the issue is where to look at the dates in the first sumproduct look at Column A dates and in the second sumproduct look at the dates in Column K???

You could adjust the reference

[spuri78]

Sorry, you are right. I didn't apply the second filter in Column A. Your formula is correct. Awesome. So, it would mean that every month I would have to manually change the month in the formula? It can't be automated, can it?

[vlady]

Nope you can use reference dell
say in Regional Report Cell F1 type the Month December then the formula will be

*(TEXT('Active Clients '!$A$2:$A$1000,"mmmm")=F1)*1

or
with out using cell reference just the system date

*(TEXT('Active Clients '!$A$2:$A$1000,"mmmm")=text(today(),"mmmm"))*1


Note: the formula looks only on Months year not included
in the first option you can "go back" to any month you like and print a report
in the second option -> it will always be the month today you cannot go "back to previous month" unless the formula is edited

[spuri78]

Awesome, Thanks for that. 1st option seems the best. So, in cell B11 for the month of Jan, I now have formula =SUMPRODUCT((('Active Clients '!$F$2:$F$1000="I/Commenced")*(TEXT('Active Clients '!$A$2:$A$1000,"mmmm")=F2)*1))--SUMPRODUCT((('Non - Active Clients'!$F$2:$F$1000="I/Commenced")*(TEXT('Non - Active Clients'!$A$2:$A$1000,"mmmm")=F1)*1)). The result is 5 instead of 6. The formula is not picking up that 1 record from Non-Active sheet.

[spuri78]

Ignore me, I worked it out. Another question, can you think of a scenario where the data could get inaccurate due to only months been included? Can you include the year?

Thanks

[vlady]

yup. say all december for the year 2014 only

you just have to change the red

*(TEXT('Active Clients '!$A$2:$A$1000,"mmmm")=F1)
to
*(TEXT('Active Clients '!$A$2:$A$1000,"mmmmyyyy")=TEXT(F1,"mmmmyyyy"))*1))

Both sumproducts

then enter December 2014 in cell F1 -> you'll notice this becomes Dec-14 when you select it and look at the formula bar become 12/1/2014

[spuri78]

That's absolutely brilliant. I'm jumping the gun here but I'm sure I'd be asked to show data for 3 months and financial year i.e. 1 July to 30 June. How can I incorporate that? I'm going to create two more worksheets for it. So, at the moment, D11 has formula =SUMPRODUCT((('Active Clients '!$F$2:$F$1000="I/Commenced")*(TEXT('Active Clients '!$A$2:$A$1000,"mmmmyyyy")=TEXT(E1,"mmmmyyyy"))*1))--SUMPRODUCT((('Non - Active Clients'!$F$2:$F$1000="I/Commenced")*(TEXT('Active Clients '!$A$2:$A$1000,"mmmmyyyy")=TEXT(E1,"mmmmyyyy"))*1))

E1 is where I have typed the month.

[vlady]

you have then multiple conditions
basically it would look like this




*((TEXT('Active Clients '!$A$2:$A$1000,"mmmmyyyy")="january")+(TEXT('Active Clients '!$A$2:$A$1000,"mmmmyyyy")="February")+(TEXT('Active Clients '!$A$2:$A$1000,"mmmmyyyy")="December"))


in shorter =(...)*( (text(cleint)="january") + (text(cleint)="february") +(text(cleint)="December") +....... )

the PLUS sign is used as an "OR"

you can use the countifs for the year report since you can use the >=date .. <= date in there)

COUNTIFS('Non - Active Clients'!F:F,"I/Commenced",'Non - Active Clients'!A:A,">=datehere",'Non - Active Clients'!A:A,"<=datehere")


Edit: BTW you just have to edit the first formula then use fill handle to drag the formula down. no need to edit one by one.

[spuri78]

Awesome, will try and let you know.

Btw, if I have another question about the same project, should I open another thread or continue in this one?

Thanks Vladimir

[vlady]

better to open another since this thread is about counting your data based on date and text criteria(I/Commenced etc.).

[spuri78]

Hi, I'm back. question about the formula to get quarterly data.

=SUMPRODUCT((('Active Clients '!$F$2:$F$1000="I/Commenced")*((TEXT('Active Clients '!$A$2:$A$1000,"mmmmyyyy")="january")+(TEXT('Active Clients '!$A$2:$A$1000,"mmmmyyyy")="February")+(TEXT('Active Clients '!$A$2:$A$1000,"mmmmyyyy")="December"))")*((TEXT('Non - Active Clients' !$A$2:$A$1000,"mmmmyyyy")="january")+(TEXT('Non - Active Clients' !$A$2:$A$1000,"mmmmyyyy")="February")+(TEXT('Non - Active Clients' !$A$2:$A$1000,"mmmmyyyy")="December")) I don't think this looks right.


So, does it mean that every quarter I would have to change the months? Is there anyway it can be automated like the monthly data? Also, in the formula, we have something like $F$2:$F$1000. Does it mean that if there are records beyond F1000, it won't count?

Thanks

EDIT: Sample Data attached

[vlady]

Why is it jan,feb and DEC month for quarterly report?
isn't it
jan
feb
mar
apr
may
june
july
aug
sept
oct
nov
dec
every 3 months consecutive?

[spuri78]

Yep, you are right. it is Jan/Feb/March....I just used it in the formula as an example.

[vlady]

my suggestion is to create 4 columns each column will contain quarterly report

for the first quarter(first column added)

=SUMPRODUCT(('Active Clients '!$F$2:$F$1000="I/Commenced")*(MONTH('Active Clients '!$A$2:$A$1000)>=1)*(MONTH('Active Clients '!$A$2:$A$1000)<=3)) + =SUMPRODUCT(('Non - Active Clients '!$F$2:$F$1000="I/Commenced")*(MONTH('Non - Active Clients '!$A$2:$A$1000)>=1)*(MONTH('Active Clients '!$A$2:$A$1000)<=3))

note the bold part
123 first quarter >= 1 and <= 3
456 second >=4 and <= 6
789 third >=7 and <=9
10,11,12 fourth >=10 and <=12 respectively

just change the bold part for every quarter

[spuri78]

Sorry, I'm trying to visualise but I can't. How will those columns look like?

[vlady]

look attachment for first quarter sample

Sample data 4.xlsm

[spuri78]

Ah! Ok, makes sense, Thanks. However, What about the taking into account the year of the quarter? e.g. for the current financial year here, Quarter 1 is 1/7/14 to 30/9/14, Quarter 2, 01/10/14 to 31/12/14, Quarter 3, 01/01/15 to 31/03/15 and Quarter 4, 01/4/15 to 30/6/15.

[vlady]

you have to imcorporate the year()

like this

=SUMPRODUCT(('Active Clients '!$F$2:$F$1000="I/Commenced")*((MONTH('Active Clients '!$A$2:$A$1000)>=1)*(YEAR('Active Clients '!$A$2:$A$1000)=2015))*((MONTH('Active Clients '!$A$2:$A$1000)<=3))*(YEAR('Active Clients '!$A$2:$A$1000)=2015))+SUMPRODUCT(('Non - Active Clients'!$F$2:$F$1000="I/Commenced")*((MONTH('Non - Active Clients'!$A$2:$A$1000)>=1)*(YEAR('Active Clients '!$A$2:$A$1000)=2015))*((MONTH('Non - Active Clients'!$A$2:$A$1000)<=3))*(YEAR('Active Clients '!$A$2:$A$1000)=2015))

note that 2015 is hardcoded you can use reference cell say E1=2015 so YEAR('Active Clients '!$A$2:$A$1000)=E1)

[spuri78]

so I guess I can have two cell references E1=2014 for quarters in 2014 and F1=2015 for quarters in 2015 right?

[vlady]

a single ref would suffice -> prior value E1 =2014

if you want a report for the year 2015 just replace the year in E1 to 2015 or any year that you like to have a report.

[spuri78]

Thanks Vladimir. I'll work on this and get back to you.

Thanks heaps.

[spuri78]

HI, I'm back. Question, in the formulas, the cell range that is specified is $F$2:$F$1000 so does it mean that if there is data in cell F1001, it won't get picked up by the formula?

[FDibbins]

Yes it does

[spuri78]

Thanks. Few more issues I'm experiencing with sheet

- All of a sudden the spreadsheet has gone really slow. If you look into Active/Non-Active sheet and when you try and filter a column, at the bottom, it says Calculating 8 processors or something. I have calculation set to automatic so not sure what's happening there.
- When I try to save the sheet, a pop message comes up regarding significant loss of functionality.
- Also, the size of the spreadsheet is large compared to some other similar sheets.

Please find the sample sheet attached.

Many thanks

[FDibbins]

When you press End/Home on some of your sheets, you end up "in the middle of nowhere"

1st thing to do, is get to where you know the last row of data on a sheet is, then delete ALL rows below that
then go to where you know the last column of data is, and delete all columns beyond that

Repeat this for the other sheets...you may need to do this in stages, do it for 1 sheet, then save, close, reopen and delete for the next sheet. I did this for sheet1 and 2, and dropped the size from over 3 meg to just over 300 k

[FDibbins]

When you press End/Home on some of your sheets, you end up "in the middle of nowhere"

1st thing to do, is get to where you know the last row of data on a sheet is, then delete ALL rows below that
then go to where you know the last column of data is, and delete all columns beyond that

Repeat this for the other sheets...you may need to do this in stages, do it for 1 sheet, then save, close, reopen and delete for the next sheet. I did this for sheet1 and 2, and dropped the size from over 3 meg to just over 300 k

[spuri78]

I did research about that but for some reason it either didn't work, too slow (especially deleting columns) or kept on getting out of resources error. However, I'm willing to give it a try again. So, the last row of data in Sheet 1 say is row 192, so delete all rows below that? for columns, delete all columns after L.

[spuri78]

ignore me, worked it out.

What about my second issue?

Thanks

[spuri78]

Also, when I switch workbook calculation to automatic and press save, I get error ''Excel ran out of resources while attempting to calculate one or more formulas...

[spuri78]

Anyone? Maybe it's the sumproduct formulas that I am using are causing the spreadsheet to slow down?

[FDibbins]

I also notice that you seem to have Conditional Formatting is some cells (and not others)? Active K99 is 1, I have not looked many other places...CF, if used excessively, will slow you down.

You do have at least 1 SP, that I found Oxley (protected sheet?) that uses full column ranges. You need to change all full-column ranges to just what you need

[spuri78]

Ok, I've gotten rid of conditional formatting in full column ranges and now I only have it in specific ranges. The file size is still big. In your posts above, you mentioned that you got rid of rows and columns and it brought down the size considerably. is there a quick way of doing it?

[FDibbins]

Highlight row 200, the press END Down, then onthe ribon Home tab, click the Delete Icon...if teh drop-down appears, select Rows

Repeat this for teh columns, starting after L

[spuri78]

That's awesome. file size down to around 400. One last issue. If you look into Active/Non-Active sheet and when you try and filter a column, at the bottom, it says Calculating 8 processors or something. I can't set the calculation to automatic so I've got it set to manual. It's really slow.

[FDibbins]

You need to do that for sheet1 AND sheet2

Also, did you see my comment in post 55 regarding the sumproduct formula/s?

[spuri78]

Sorry, I thought I had gotten rid of full column ranges. Fixed them and now I'm not getting calculating message. However, when I save, I get significant lost of functionality error. Sample sheet attached.

Thanks

[FDibbins]

I got the same msg, but thought it might be because I am using 2007, while you have 2013

I am not sure what functionality (if any) you would lose by saving as a binary file, but now that you have made the WB smaller and faster, maybe try saving as a regular ;xlsx file>

[spuri78]

that's cool, as long as it's just a message and I won't lose any functionality, it's all good. Thanks very much for all your help. this is a work in progress so I will come back if I have any issues as I need to set up summary sheet for Quarterly and Financial Year data.

[spuri78]

Ok, I'm noticing another strange issue. Sample sheet attached. If you have a look in Region Report_Monthly tab cell B11 to B47, the column range keeps on changing from $F$2:$F$1000/$A$2:$A$1000 to $F$2:$F$987/$A$2:$A$987. It's the second time that has happened now since yesterday. Do you know what could be causing it?

[FDibbins]

Have you deleted any rows within that range...ie abobe row 1000?

[FDibbins]

Looking at thoswe formulas, perhaps this might be better for a lot of them?

Add a helper (I used F) and copy this down...
=LEFT(A11,FIND(" (Active",A11,1)-1)
It wont work as-is for all of the rows, but you can fine-tune it as you go down (for rows 24:29, I manually entered "accepted etc).

Then, instead of that big sumproduct, use this, with that helper...
=COUNTIFS('Active Clients '!F:F,F11,'Active Clients '!A:A,">="&$E$1,'Active Clients '!A:A,"<"&EDATE($E$1,1))+COUNTIFS('Non - Active Clients'!F:F,F11,'Non - Active Clients'!A:A,">="&$E$1,'Non - Active Clients'!A:A,"<"&EDATE($E$1,1))

change the bolded reference as needed

[spuri78]

I'm pretty sure I haven't deleted anything above row 1000. Sorry, I don't understand the helper bit and having hard time visualising it. Do you showing it in the sample sheet?

Thanks

[FDibbins]

A helper column is just a spare column you use to do calcs in that will "assist" or help other formulas, they are used to keep other formulas simple and easier to understand.

Your file is a bit too big to upload, I will return shortly

[FDibbins]

OK, I have attached the WB. I left your answers in plave, and put my formulas in column G.

Once you are OK with them, dont just copy/paste, copy from the Formula Editing Bar from G11, then click on paste to the F.E.B. in B11, then copy/paste that down

[spuri78]

Thanks for that. Just a quick look in cell B20. The result should be 35. I'll keep checking the other cells.

[rohitsingh]

I found the formula needed on my question. No action required.