• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Making a criteria as variable inside COUNTIFS formulae

A

Anirban

New Member
Hi Chandoo,

I have a file in which there are two sheets "Sheet1"& "Sheet2". In Sheet1 we paste a raw data from an external file (another excel file). In Sheet2 there is a formulae defined which will count the number of a specific item as per date (e.g. 1st Jan has 20 black leather jackets, 2nd Jan has 50 black leather jackets and so on) from Sheet1. Now the challenge is:

As i mentioned earlier, in Sheet1 we are putting the data from another excel file where the data in only for one month, Now if i put next month's data in Sheet1 (which i am getting from another external excel file) the changes are not reflecting in Sheet2 as in the formulae of COUNTIFS, the criteria is given for a particular date of previous month. In order to make the changes in need to edit the date in the formulae for all cells in Sheet2 The formulae is:

=COUNTIFS(Dump!$C$1:$C$5000,"Black",Dump!$F$1:$F$5000,"Leather Jacket",Dump!$Q$1:$Q$5000,"01/01/2016")

Here every criteria range and criteria is taking from a Sheet called Dump (Sheet1 renamed as 'Dump') and as you can see that the date is 1st Jan 2016. Now the moment date is changing to 01/02/2016 in Sheet1 the change is not reflecting in the current Sheet (Sheet2). Is there any way that i can make the date part variable in countifs formulae, like the moment i put the new months data in Sheet1 the date will change in the countifs formulae in the next sheet.

Note - the date format is DD/MM/YYYY

Hope i am able to explain the problem that i am facing.

Thanks and regards
Anirban
 
What I generally do is set up Date column in Sheet2 and use that as reference and pull all data matching Date cell.

Either use VBA or paste special->Value->Add to update dates. So instead of static "1/1/2016", I use $A2 or whatever the column date is stored in sheet2.
 
@Narayan - The data is for the month of Jan and there are total 1123 entries
100 for 1st Jan, 200 for 2nd Jan till 31st Jan etc. Total comes to 1123 entries.

@Chihiro - Thanks you Chihiro. If possible can you provide the formula.
 
Hi Anirban ,

In that case , try the following :

=COUNTIFS(Dump!$C$1:$C$5000,"Black",Dump!$F$1:$F$5000,"Leather Jacket",Dump!$Q$1:$Q$5000,DATE(2016,1,ROW(A1)))

Copying this down will change the date to Jan-2 , Jan-3 and so on.

Narayan
 
You must log in or register to reply here.
Back
Top