• 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.

Countifs in a table to count in sequence

A

aggie81

Member
I want to count each occurrence of an item in one column (Item Number) based on more than one variable to give the sequence of the item, 1, 2, 3, 4, 5, 6, etc. using table structure.
I use COUNTIF(INDEX([Item Number],1):[@[Item Number]],[@[Item Number]])
and have included a file with the formula in the CropSPR and CropSFW columns. This works well enough but can only handle one variable.
Sometimes we aren't able to plant a crop because of disease, time, or something else.
I have tried using SUMPRODUCT but couldn't get it to work.
Thanks for your help and thoughts,
Lee
 

Attachments

  • Countif to Countifs.xlsx
    215.1 KB · Views: 14
Hi ,

I am not able to understand what your requirement is.

Can you explain what you want as the output , based on which inputs , all in terms of worksheet / table cells / columns , instead of using terminology such as disease , time ,... ?

Narayan
 
Sorry for not making myself understood.
Is it possible to make COUNTIFS() with multiple criteria do the same as
COUNTIF(INDEX([Item Number],1):[@[Item Number]],[@[Item Number]])
with one criteria?
In the CropSPR column has one of the COUNTIF() formula with several IF() statements in front of it. I want/need to add another criteria but with all the IF() it is hard to read and thought COUNTIFS() would be easier but can't make it work.
Thanks,
Lee
 
Hi ,

I am sorry , but I do not have the inclination to go through your formula and understand what you want to do.

If you can explain in simple English what you want done , not just me , but anyone else can suggest the right formula to do the job.

Narayan
 
Using a table format, can COUNTIFS() be made to count each instance in a column based on multiple criteria with the number of that instance instead of giving how many instances? It would give 1, 2, 3 ,4, 5 instead of a total 5 occurrences.
Thanks,
Lee
 
Hi ,

That is never a problem.

A simple COUNTIF will return either the number of the current instance , or the total number of instances ; it all depends on the range that is passed to the function.

Suppose you wish to find the total number of instances of the text string "No" in a data range A4:A13 ; the following formula will do this :

=COUNTIF($A$4:$A$13 , "No")

Now , suppose you wish to number each instance of the text string "No" in this same range ; you would now enter the following formula in any unused column , and copy it down.

=COUNTIF($A$4:$A4 , "No")

This would return 0 if A4 did not have the looked for text string in it , otherwise it would return 1.

As you copy it down , the formula would change as follows :

=COUNTIF($A$4:$A5 , "No")

=COUNTIF($A$4:$A6 , "No")

=COUNTIF($A$4:$A7 , "No")

=COUNTIF($A$4:$A8 , "No")

As and when the next instance of the text string is found , the count would be incremented.

Narayan
 
Yes I know that COUNTIF() will do that based on ONE criteria and the range being passed as you say. It is the same as I showed in my first post but the formula is inside a TABLE.
The data is in TABLE format so the person using it has all the formulas added when they add more ROWS to the bottom on the TABLE.
I use INDEX() inside the COUNTIF() to pin the starting point using COUNTIF() hence the INDEX([column],1:[@[first cell, column],[@[first cell, column]). Zach Barresse is the one who taught how to use INDEX() to pin absolute to a cell inside a table.
I can't get it to work using COUNTIFS() because I need multiple CRITERIA.
I am trying to use the correct TABLE syntax when using TABLES because I have had problems using cell references when adding ROWS at the end of the TABLE.
I appreciate the help.
Thanks,
Lee
 
In your sample file clearly indicate which columns are to be used as condition and what your expected out put would be (and where). I'm having trouble understanding what the problem is.
 
Let's try this another way.
If I put this in one of the cells and copy down it will count in sequence:
=COUNTIFS(INDEX([Item Number],1):[@[Item Number]],[@[Item Number]])
but if I try to add criteria then I get #VALUE error except for the last cell that will give me a total
=COUNTIFS(INDEX([Item Number],1):[@[Item Number]],[@[Item Number]],[Projected],"=90")
Lee
 
aggie81 said:
Let's try this another way.
If I put this in one of the cells and copy down it will count in sequence:
=COUNTIFS(INDEX([Item Number],1):[@[Item Number]],[@[Item Number]])
but if I try to add criteria then I get #VALUE error except for the last cell that will give me a total
=COUNTIFS(INDEX([Item Number],1):[@[Item Number]],[@[Item Number]],[Projected],"=90")
Lee
Click to expand...
Hi ,

Now it is perfectly clear what you are trying to do , and it is also perfectly clear why that is not working !

When you use multiple conditions in a COUNTIFS function , you are basically operating on multiple arrays.

Here , the first array is one which contains a variable number of elements , starting with one element in the first formula , two elements in the next copied down formula , three in the next copied down formula , and so on.

The second array however , is a fixed array , having as many elements as there are rows in the table.

Operating on these two dissimilar arrays will generate the error that Excel is displaying.

Either you use the same technique for every additional array you incorporate in the COUNTIFS , or you use some other method to get what you want.

Narayan
 
I used INDEX() in the other criteria ranges and don't get #VALUE error.
But it doesn't give the correct answer.
I will keep trying and want to say thanks to all for reading and helping.
Lee
 
Hi ,

The following formula works correctly :

=COUNTIFS(INDEX([Item Number],1):[@[Item Number]],[@[Item Number]],INDEX([Projected],1):[@Projected],90)

You have done everything but post your COUNTIFS formula. If you do that , it might clear things up a bit.

Narayan
 
=COUNTIFS(INDEX([Item Number],1):[@[Item Number]],[@[Item Number]],INDEX([Planted],1):[@Planted],"<>0",INDEX([Season],1):[@Season],"=SFW")
in the NumCropsPlntd column.
It should skip the zero in the Planted column and give sequence count of total crops. ????
Count the number of times the Item Number has a number not 0 and the Season equals to SFW.
Lee
 
Try this.
=COUNTIFS(INDEX([Item Number],1):[@[Item Number]],[@[Item Number]],INDEX([Planted],1):[@Planted],"<>0",INDEX([Season],1):[@Season],"SFW")*([@Planted]<>0)
 
Narayan, why is it counting the Planted columns with zero as part of the sequence?
It should write a zero in those that match and continue with the sequence at 5 instead there are three cells that have 4 in the file that Narayan refers.
???
Chihiro, your formula works!!!
Thanks to both of you and your patience with me! I can't begin to tell you how much this helps! I'm glad for this forum and the people in here.
Lee
 
Without *([@Planted]<>0) portion, you are doing cumulative count. Therefore, even if the row does not increment the count, it will count all the rows that meet criteria (prior to that row) and will return that number (i.e. 4).

By multiplying the result with True/False (1/0) you can zero out the result when it does not meet the condition.
 
Chihiro, I tried and found that for each criteria range, criteria that is added you must * that criteria on the last criteria statement for COUNTIFS() to sequence.
Cool, thanks again.
 
You must log in or register to reply here.
Back
Top