Thread: Changing COUNTIF formula

I'm fairly new to Excel, although I understand all of the basic stuff very well.

An employee who has since left us created a file for us using the following formula to count the number of occurrences of a specific sales item in our sales history per month:

=COUNTIF('Feb 2011'!$J:$J,"*"&'Monthly Summary'!B5&"*")+COUNTIF('Feb 2011'!$J:$J,"*"&'Monthly Summary'!B5&" (2)"&"*")+(2*(COUNTIF('Feb 2011'!$J:$J,"*"&'Monthly Summary'!B5&" (3)"&"*")))

I believe that I understand everything about this formula except, why is there a (2*(COUNTIF in the third string?

Also: I want to basically copy this file but look at day-by-day sales of certain items instead of month-by-month.

So I created a new file, copied all of the data in, renamed the tab "Monthly Summary". Then I created another tab and pasted the data from the original "Feb 2011" file. I renamed it Oct 1, cleared all of the February data out, and pasted in the sales data from Oct 1.

The count formula is not working correctly though, the numbers it's giving me are way off. Is there anything else I need to think of changing, anything I'm missing that must be specific to the first file only?

I assume that figure in brackets is a count so assuming that Monthly Summary'!B5 contains the value "xxx" the first COUNTIF counts all entries which contain "xxx".....the second COUNTIF counts all entries that contain "xxx (2)" and the third counts those that contain "xxx (3)". Now the first one will, by definition, also count the latter two, so by multiplying the third by 2 you get a single count of "xxx" a double count of "xxx (2)" (one from 1st COUNTIF one from second) and a triple count of "xxx (3)" (one from 1st COUNTIF two from third)

You can make it shorter with this version

=SUM(COUNTIF('Feb 2011'!$J:$J,"*"&'Monthly Summary'!B5&{"*"," (2)*"," (3)*"})*{1,1,2})