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

COUNTIF COUNTIFS [SOLVED]

E

eoinmc

New Member
Problem - I have 2 COUNTIF functions that work but I cannot combine them into a COUNTIFS function based on the same data.


I have a list of events with the participants. Participants can take part in more than one event. I want to count how many events someone has participated in since a given date.


Using COUNTIF, I can find the total events that happened since a given date. Using another COUNTIF, I can find the total number of participations by a given person.

But I cannot get a COUNTIFS function to work using the criteria that worked separately in the COUNTIF functions. It returns a #¡VALOR! warning.

What I don't understand is if the COUNTIF functions work, why does the COUNIFS funtion

not work if it is the same data?

Note that I am working with Excel in Spanish so the formulae will look different. Also that Excel in Spanish uses ";" rather than ",".


COUNTIF to find total events since given date: =CONTAR.SI(Date_Full_Inspection:Date_Interim_Audit;">="&$P$2)


COUNTIF to find total participations for a given name

=CONTAR.SI(Inspectors_names;A303)


Any advice gratefully received.
 
Hi ,


I am not able to visualize your data layout , but I'll tell you what I did , and you can see if it applies to your worksheet.


Suppose the data layout is as follows :

[pre]
Code: 
Events     Dates     Participants
[/pre]
in 3 columns ; suppose the cut-off date and the selected participant are two named cells ; then the following formula , entered as an array formula , using CTRL SHIFT ENTER , will give the number of events the selected participant attended on dates greater than or equal to the cut-off date :


=SUM(IF((Dates>=Cut_off_date)*(Participants=Selected_participant),1/COUNTIFS(Dates,">="&Cut_off_date,Participants,Selected_participant,Events,Events)))


I have assumed that there will be many participants in each event.


Narayan
 
Thanks for taking the time to answer but I'm afraid it didn't work for me. I've lined up my ranges with the data labels you used but I don't understand all of the COUNTIFS function. I get the following: Range of Dates; more than the Cut-off date; Range of Participants; Specific participant;

But I don't get the "Events;Events.." part. Is the criteria for the Range of Events the range of events itself?


This is the array formula.


{=SUM(SI((Date_Full_Inspection:Date_Interim_Audit>=$P$2)*(Inspectors_names=A7);1/CONTAR.SI.CONJUNTO(Date_Full_Inspection:Date_Interim_Audit;">="&$P$2;Inspectors_names;A7;Date_Full_Inspection;Date_Full_Inspection)))}


The file can be accessed at https://www.dropbox.com/s/92iknu14uhitdjv/Copia%20de%20Inspections%20and%20inspectors.xlsx


Thank you.
 
Hi ,


Can you check your file here ?


https://www.dropbox.com/s/a9xyrb3j28e2n2a/Copia%20de%20Inspections%20and%20inspectors.xlsx


I have revised the definitions of the named ranges , and changed the formulae in columns P and Q.


It will be better if you copy the named range definitions and the formulae to the file which you have ; this is just to ensure that the data is unchanged.


Narayan
 
Dear Narayan,

This looks excellent. Regardless, I am very grateful. Sincere thanks for taking the time and effort.

Best wishes,

Eoin
 
Dear Narayan,
Last year you very kindly resolved a problem for me. I am looking at the data again and made some changes and unfortunately messed up the formula. Do you by any chance have the same Excel file? The Dropbox link shows as closed.
 
Thanks Narayan. I cannot replicate the formula from last year's file. The only thing that occurs to me is that there is a problem in the MMULT part of the formula but since it works in the "Copia...." file, I have no clue of how to fix it in the new file "Inspectors and Inspections 4". The link to this new file is https://dl.dropboxusercontent.com/u/11182396/Inspections and inspectors 4.xlsx.
Could you have a look and see where I have gone wrong with this?
best wishes,
 
So it was a small detail in the end. Thank you very much.
Could I ask you what "...{1;1;1;1;1;1;1;1;1}))>0);1;0)) ..." does in the formula? Is it related to columns in the data?
Eoin
 
Hi ,

The array of 1s is because we have 9 columns from Inspector1 through Inspector8 and then IA_Clin_Inspector.

When you do matrix multiplication , the number of columns in the first matrix and the number of rows in the second matrix should match. So because there are 9 columns in the first matrix , there have to be an equal number of rows in the second matrix.

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