Excel Forumla

[MatthewL]

I have 2 spreadsheets, incidents and statistics for agruments sake. In incidents there is a worksheet with the year i.e. 2023.

 

Incidents containts data and statistics pulls figures over. In incidents colum C has text like DOG CAT BEAR MILK WATER WATER-RE CAT-NIP entered, could be in any order, not in alphabetical but just as its enter.

 

I have an issue where these forumlas are not quite working correctly, they basically sum up the times the above is used i.e. DOG = 1, MILK = 1.

 

On incidents in tab called stats this look at 2023 and sums them

=COUNT(IF(ISERROR(FIND(B80,'2023'!$C$2:$C$30000,1)), "",1))

 

On statistics this queries the above and does the same but does it based on date.

=COUNTIFS('[incidents.xlsx]2023'!$E:$E,">="&C$1,'[incidents.xlsx]2023'!$E:$E,"<"&EDATE(D$1,0),'[incidents.xlsx]2023'!$C:$C,$A41)

C could be 01/01/2023

D could be 01/02/2023

So it would look at dates between.

 

Incidents - colum E is date

Incidents - colum C is the one that has dog cat etc in

A41 at the end is a call that has said value in.

 

Hope that makes sense so far.

 

Basically both formulas should add up the same, first is just a total, second has a sum at the end of it but they dont match and its because its not liking things in a mixed order. Can I modify my formula to count regardless of its order?

[mavhc]

Can you post a demo file?

 

Sounds like something a pivot table should be used for

[MatthewL]

Not really with ease.

 

I literally want to count how many times said phrase is mentioned regardless of the order its entered. The second one queries the date which is in another colum and in the main it works its just seems to be the order thats the issue when the dates are included.

[andy_b]

Not really sure I understand, but:

 

=COUNTIF(A1:A10,"*milk*")

 

should do a count if the cell "contains" milk.

 

If that is relevant lol..