Fitness Sheet COUNTING help

[thestones757]

Hey guys, I am trying to motivate myself in anyway possible, I figured by creating an excel sheet and seeing some statistics might help, worth a shot.

I would post a screen shot but it is way too hard. Anyways, I have a tab for data where I have a column with each date for the remainder of the year, other columns for the corresponding month, week, and day numbers (I thought would be useful for future analysis). Along with this I have different columns with one being miscellaneous sports where I enter Basketball, Baseball, Football, etc. Now under the anylsis tab I want to count how many times I play each sport for each month. So I have set up a table with the remaining months of the year and inserted columns with the miscellaneous sports titles.

If I was trying to count numbers it would be easier, but I have to incorporate some type of COUNT statement in order to count the number of times the data table both has the specific month and also the specific sport in the particular heading.

I have looked up a lot of info on this issue and have come up with the following attempt:

=COUNT(IF(Data!$E$3:$E$204="*"&L$2&"*",IF(Data!$B$3:$B$204=B4,Data!$E$3:$E$204)))

Data is the data table:
E column of the Data table is the "Sport" (previously referenced as miscellaneous sports)
B column of the Data table is the "Month"

Analysis is the analysis table:
B4 is the month being analyzed
L2 is the sport being counted


this does not do what I want it to. I was wondering if anyone knew what I could do? I think the solution is multiplying "TRUE" and "FALSE" (1 and 0) statements together so that unless both statements are true the count will be 0 but I am not sure how to set it up. Any help is greatly appreciated!

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(--(Data!$B$3:$B$204=B4),--(Data!$E$3:$E$204=L$2))

 

[thestones757]

It did not work, but I think that is a great idea, something must be off with the arrays for the SUMPRODUCT though is there another way that you can incorporate the two conditions?

 

[Aladin Akyurek]

It did not work, but I think that is a great idea, something must be off with the arrays for the SUMPRODUCT though is there another way that you can incorporate the two conditions?

Would you...

a) Stipulate the ranges;
b) Stipulate the conditions that must hold for the ranges in (a)?

 

[thestones757]

I think you had the right idea but:

1) Data!$E$3:$E$204="*"&L$2&"*"
or
Data!$E$3:$E$204="Basketball" (for Basketball)

2) Data!$B$3:$B$204=B4
or
Data!$B$3:$B$204=6 (for June)

Thanks!

 

[Aladin Akyurek]

I think you had the right idea but:

1) Data!$E$3:$E$204="*"&L$2&"*"
or
Data!$E$3:$E$204="Basketball" (for Basketball)

2) Data!$B$3:$B$204=B4
or
Data!$B$3:$B$204=6 (for June)

Thanks!

If Basketball does also occur as part of a string in Data!$E$3:$E$204 and
Data!$B$3:$B$204 houses month numbers (not month names) with B4 = 6...

=SUMPRODUCT(--(Data!$B$3:$B$204=B4),--ISNUMBER(SEARCH(L$2,Data!$E$3:$E$204)))

would be appropriate.

 

[thestones757]

Thanks so much!

 

[Aladin Akyurek]

Thanks so much!

You are welcome. Thanks for providing feedback.