Douglas Edward

New Member
Joined
Jul 24, 2018
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
Is it possible to make this "Golf Course Count Table" (on the right) without the "Day" cheater column? and just get the info from the "Date" column?

ACADAEAFAGAHAIAJAKALAMAN
3DateDayTimeLocationTournamentPlayersCourseWednesdayFridaySaturdayTotal
4Friday, January 3, 2020Friday7amWilsonNew Year's Super Skins60Wilson26715
5Saturday, January 11, 2020Saturday7amHardingLeaning Tree Classic60Harding35614
6Wednesday, January 22, 2020Wednesday7amHarding3/6 Scramble32LA City Away0112
7Saturday, February 8, 2020Saturday7amWilsonThe Bambino60Total5121431
8Friday, February 21, 2020Friday7amWilson1457: Banned by Law60
9Begins in MarchFourball Championship96
10Saturday, March 7, 2020Saturday7amHardingThe Rogow72
11Wednesday, March 18, 2020Wednesday7amHardingSt. Patty's Shamble84
12Friday, March 27, 2020Friday7amWilsonArnie's Army60
13Saturday, April 4, 2020Saturday7amWilsonGPGC 36 Hole Championship Day 184
14Sunday, April 5, 2020Sunday7amHardingGPGC 36 Hole Championship Day 284
15Friday, April 24, 2020Friday7amHardingThe Didrikson32
16Saturday, May 2, 2020SaturdayIndian Canyons South8th Annual Palm Springs Desert Classic40
17Sunday, May 3, 2020SundayDesert Willow Firecliff8th Annual Palm Springs Desert Classic40
18Friday, May 15, 2020Friday7amHansen DamThe Dam60
19Saturday, May 30, 2020Saturday7amHardingMarty Tregnan Memorial84
20Saturday, June 20, 2020Saturday7amWilsonThe Open84
21Wednesday, June 24, 2020Wednesday7amWilsonThe International60
22Friday, July 3, 2020Friday7amHardingRed White and Blue64
23Saturday, July 11, 2020Saturday7amEncinoEncino Man84
24Saturday, August 1, 2020Saturday7amHardingSNIEGOWSKI XI84
25Wednesday, August 12, 2020Wednesday7amWilsonThe Tune-Up32
26Friday, August 21, 2020Friday7amHarding83rd Club Championship Qualifier120
27Saturday, August 22, 2020Saturday7amWilson83rd Club Championship Qualifier120
28Friday, September 4, 2020Friday7amWilsonThe Club Championship & Championship Sunday32
29Saturday, September 5, 2020Saturday7amHardingThe Club Championship & Championship Sunday16
30Sunday, September 6, 2020Sunday7amWilsonThe Club Championship & Championship Sunday60
31Saturday, September 19, 2020Saturday7amHardingFall Season Kickoff84
32Saturday, October 3, 2020Saturday7amWilsonOktoberfest32
33Friday, October 16, 2020Friday7amWilsonThe President's Cup24
34Friday, October 16, 2020Friday1pmHardingThe President's Cup24
35Saturday, October 17, 2020Saturday7amWilsonThe President's Cup24
36Friday, October 30, 2020Friday7amHardingHalloween84
37Saturday, November 14, 2020Saturday7amWilsonTurkey Shoot84
38Wednesday, November 25, 2020Wednesday7amHarding3 Club Challenge32
39Friday, December 11, 2020Friday7amWilsonChristmas Shotgun128

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
AK4=COUNTIFS(Table15[Location],AJ4,Table15[Day],Table7[[#Headers],[Wednesday]])
AL4=COUNTIFS(Table15[Location],AJ4,Table15[Day],AL3)
AM4=COUNTIFS(Table15[Location],AJ4,Table15[Day],AM3)
AN4=SUM(Table7[@[Wednesday]:[Saturday]])
AK5=COUNTIFS(Table15[Location],AJ5,Table15[Day],AK3)
AL5=COUNTIFS(Table15[Location],AJ5,Table15[Day],AL3)
AM5=COUNTIFS(Table15[Location],AJ5,Table15[Day],AM3)
AN5=SUM(Table7[@[Wednesday]:[Saturday]])
AK6=COUNTIFS(Table15[Location],AA4,Table15[Day],Table7[[#Headers],[Wednesday]])+COUNTIFS(Table15[Location],AA6,Table15[Day],Table7[[#Headers],[Wednesday]])
AL6=COUNTIFS(Table15[Location],AA4,Table15[Day],Table7[[#Headers],[Friday]])+COUNTIFS(Table15[Location],AA6,Table15[Day],Table7[[#Headers],[Friday]])
AM6=COUNTIFS(Table15[Location],AA4,Table15[Day],Table7[[#Headers],[Saturday]])+COUNTIFS(Table15[Location],AA6,Table15[Day],Table7[[#Headers],[Saturday]])
AN6=SUM(Table7[@[Wednesday]:[Saturday]])
AK7=SUM(AK4:AK6)
AL7=SUM(AL4:AL6)
AM7=SUM(AM4:AM6)
AN7=SUM(Table7[@[Wednesday]:[Saturday]])

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Re: Need help with countifs formula

yes, try this

=SUMPRODUCT((IFERROR(FIND(AK$3,$AC$4:$AC$39),0)=1)*($AF$4:$AF439=$AJ4))
double click in the formula once its in cell J2 and use ctrl + Shift + enter


Excel 2010
ABCDEFGHIJKL
13DateDayTimeLocationTournamentPlayersCourseWednesdayFridaySaturday
24Friday, January 3, 2020Friday7amWilsonNew Year's Super Skins60Wilson267
35Saturday, January 11, 2020Saturday7amHardingLeaning Tree Classic60Harding356
46Wednesday, January 22, 2020Wednesday7amHarding3/6 Scramble32
57Saturday, February 8, 2020Saturday7amWilsonThe Bambino60
68Friday, February 21, 2020Friday7amWilson1457: Banned by Law60
79Begins in MarchFourball Championship96
810Saturday, March 7, 2020Saturday7amHardingThe Rogow72
911Wednesday, March 18, 2020Wednesday7amHardingSt. Patty's Shamble84
1012Friday, March 27, 2020Friday7amWilsonArnie's Army60
1113Saturday, April 4, 2020Saturday7amWilsonGPGC 36 Hole Championship Day 184
1214Sunday, April 5, 2020Sunday7amHardingGPGC 36 Hole Championship Day 284
1315Friday, April 24, 2020Friday7amHardingThe Didrikson32
1416Saturday, May 2, 2020SaturdayIndian Canyons South8th Annual Palm Springs Desert Classic40
1517Sunday, May 3, 2020SundayDesert Willow Firecliff8th Annual Palm Springs Desert Classic40
1618Friday, May 15, 2020Friday7amHansen DamThe Dam60
1719Saturday, May 30, 2020Saturday7amHardingMarty Tregnan Memorial84
1820Saturday, June 20, 2020Saturday7amWilsonThe Open84
1921Wednesday, June 24, 2020Wednesday7amWilsonThe International60
2022Friday, July 3, 2020Friday7amHardingRed White and Blue64
2123Saturday, July 11, 2020Saturday7amEncinoEncino Man84
2224Saturday, August 1, 2020Saturday7amHardingSNIEGOWSKI XI84
2325Wednesday, August 12, 2020Wednesday7amWilsonThe Tune-Up32
2426Friday, August 21, 2020Friday7amHarding83rd Club Championship Qualifier120
2527Saturday, August 22, 2020Saturday7amWilson83rd Club Championship Qualifier120
2628Friday, September 4, 2020Friday7amWilsonThe Club Championship & Championship Sunday32
2729Saturday, September 5, 2020Saturday7amHardingThe Club Championship & Championship Sunday16
2830Sunday, September 6, 2020Sunday7amWilsonThe Club Championship & Championship Sunday60
2931Saturday, September 19, 2020Saturday7amHardingFall Season Kickoff84
3032Saturday, October 3, 2020Saturday7amWilsonOktoberfest32
3133Friday, October 16, 2020Friday7amWilsonThe President's Cup24
3234Friday, October 16, 2020Friday1pmHardingThe President's Cup24
3335Saturday, October 17, 2020Saturday7amWilsonThe President's Cup24
3436Friday, October 30, 2020Friday7amHardingHalloween84
3537Saturday, November 14, 2020Saturday7amWilsonTurkey Shoot84
3638Wednesday, November 25, 2020Wednesday7amHarding3 Club Challenge32
3739Friday, December 11, 2020Friday7amWilsonChristmas Shotgun128
Sheet1
Cell Formulas
RangeFormula
J2{=SUMPRODUCT((IFERROR(FIND(J$1,$B$2:$B$37),0)=1)*($E$2:$E$37=$I2))}
J3{=SUMPRODUCT((IFERROR(FIND(J$1,$B$2:$B$37),0)=1)*($E$2:$E$37=$I3))}
K2{=SUMPRODUCT((IFERROR(FIND(K$1,$B$2:$B$37),0)=1)*($E$2:$E$37=$I2))}
K3{=SUMPRODUCT((IFERROR(FIND(K$1,$B$2:$B$37),0)=1)*($E$2:$E$37=$I3))}
L2{=SUMPRODUCT((IFERROR(FIND(L$1,$B$2:$B$37),0)=1)*($E$2:$E$37=$I2))}
L3{=SUMPRODUCT((IFERROR(FIND(L$1,$B$2:$B$37),0)=1)*($E$2:$E$37=$I3))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Re: Need help with countifs formula

ps. I was guessing the number on the far left was supposed to be the row number for you. so I adjusted the top formula to reflect that
 
Upvote 0
Re: Need help with countifs formula

Here is a way using COUNTIFS.
Mine shows 0 for LA City as there were none in the data you posted. Also, I only copied back a small part of the data into the forum.
Copy the formula down and across as needed.
Excel Workbook
ACADAEAFAGAHAIAJAKALAMAN
3DateDayTimeLocationTournamentPlayersCourseWednesdayFridaySaturdayTotal
4Friday, January 3, 2020Friday7amWilsonNew Year's Super Skins60Wilson267
5Saturday, January 11, 2020Saturday7amHardingLeaning Tree Classic60Harding356
6Wednesday, January 22, 2020Wednesday7amHarding3/6 Scramble32LA City Away000
7Saturday, February 8, 2020Saturday7amWilsonThe Bambino60Total
8Friday, February 21, 2020Friday7amWilson1457: Banned by Law60
9Begins in MarchFourball Championship96
10Saturday, March 7, 2020Saturday7amHardingThe Rogow72
Sheet
 
Upvote 0
Re: Need help with countifs formula

I'm doing it wrong, but I don't know why. Ha I enter =SUMPRODUCT((IFERROR(FIND(J$1,$B$2:$B$37),0)=1)*($E$2:$E$37=$I2)) into cell J2, then double click the formula, then Ctrl+Shift+Enter to get the curly braces. But I'm getting a incorrect answer of 0.


Book1
BCDEFGHIJKLM
1DateDayTimeLocationTournamentPlayersCourseWednesdayFridaySaturdayTotal
2Friday, January 3, 2020Friday7amWilsonNew Year's Super Skins60Wilson0000
3Saturday, January 11, 2020Saturday7amHardingLeaning Tree Classic60Harding0
4Wednesday, January 22, 2020Wednesday7amHarding3/6 Scramble32LA City Away0
5Saturday, February 8, 2020Saturday7amWilsonThe Bambino60Total0000
6Friday, February 21, 2020Friday7amWilson1457: Banned by Law60
7Begins in MarchFourball Championship96
8Saturday, March 7, 2020Saturday7amHardingThe Rogow72
9Wednesday, March 18, 2020Wednesday7amHardingSt. Patty's Shamble84
10Friday, March 27, 2020Friday7amWilsonArnie's Army60
11Saturday, April 4, 2020Saturday7amWilsonGPGC 36 Hole Championship Day 184
12Sunday, April 5, 2020Sunday7amHardingGPGC 36 Hole Championship Day 284
13Friday, April 24, 2020Friday7amHardingThe Didrikson32
14Saturday, May 2, 2020SaturdayIndian Canyons South8th Annual Palm Springs Desert Classic40
15Sunday, May 3, 2020SundayDesert Willow Firecliff8th Annual Palm Springs Desert Classic40
16Friday, May 15, 2020Friday7amHansen DamThe Dam60
17Saturday, May 30, 2020Saturday7amHardingMarty Tregnan Memorial84
18Saturday, June 20, 2020Saturday7amWilsonThe Open84
19Wednesday, June 24, 2020Wednesday7amWilsonThe International60
20Friday, July 3, 2020Friday7amHardingRed White and Blue64
21Saturday, July 11, 2020Saturday7amEncinoEncino Man84
22Saturday, August 1, 2020Saturday7amHardingSNIEGOWSKI XI84
23Wednesday, August 12, 2020Wednesday7amWilsonThe Tune-Up32
24Friday, August 21, 2020Friday7amHarding83rd Club Championship Qualifier120
25Saturday, August 22, 2020Saturday7amWilson83rd Club Championship Qualifier120
26Friday, September 4, 2020Friday7amWilsonThe Club Championship & Championship Sunday32
27Saturday, September 5, 2020Saturday7amHardingThe Club Championship & Championship Sunday16
28Sunday, September 6, 2020Sunday7amWilsonThe Club Championship & Championship Sunday60
29Saturday, September 19, 2020Saturday7amHardingFall Season Kickoff84
30Saturday, October 3, 2020Saturday7amWilsonOktoberfest32
31Friday, October 16, 2020Friday7amWilsonThe President's Cup24
32Friday, October 16, 2020Friday1pmHardingThe President's Cup24
33Saturday, October 17, 2020Saturday7amWilsonThe President's Cup24
34Friday, October 30, 2020Friday7amHardingHalloween84
35Saturday, November 14, 2020Saturday7amWilsonTurkey Shoot84
36Wednesday, November 25, 2020Wednesday7amHarding3 Club Challenge32
37Friday, December 11, 2020Friday7amWilsonChristmas Shotgun128
Sheet3
Cell Formulas
RangeFormula
M2=SUM(Table714[@[Wednesday]:[Saturday]])
M3=SUM(Table714[@[Wednesday]:[Saturday]])
M4=SUM(Table714[@[Wednesday]:[Saturday]])
M5=SUM(Table714[@[Wednesday]:[Saturday]])
J5=SUM(J2:J4)
J2{=SUMPRODUCT((IFERROR(FIND(J$1,$B$2:$B$37),0)=1)*($E$2:$E$37=$I2))}
K5=SUM(K2:K4)
K2{=SUMPRODUCT((IFERROR(FIND(K$1,$B$2:$B$37),0)=1)*($E$2:$E$37=$I2))}
L5=SUM(L2:L4)
L2{=SUMPRODUCT((IFERROR(FIND(L$1,$B$2:$B$37),0)=1)*($E$2:$E$37=$I2))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Re: Need help with countifs formula

Thanks for your reply AhoyNC. Your countif formula is basically the same as mine. I'm trying to delete the Day Column, and just use the Date column.

And the La City courses are Hansen Dam & Encino.
 
Upvote 0
Re: Need help with countifs formula

Try:
Excel Workbook
ACADAEAFAGAHAIAJAKALAMAN
3DateDayTimeLocationTournamentPlayersCourseWednesdayFridaySaturdayTotal
4Friday, January 3, 2020Friday7amWilsonNew Year's Super Skins60Wilson267
5Saturday, January 11, 2020Saturday7amHardingLeaning Tree Classic60Harding356
6Wednesday, January 22, 2020Wednesday7amHarding3/6 Scramble32LA City Away000
7Saturday, February 8, 2020Saturday7amWilsonThe Bambino60Total
8Friday, February 21, 2020Friday7amWilson1457: Banned by Law60Friday
9Begins in MarchFourball Championship962
10Saturday, March 7, 2020Saturday7amHardingThe Rogow72267
Sheet
 
Upvote 0
Re: Need help with countifs formula

The formula I gave you in post 7 above will only work if your dates in AC are text. If they are actual Excel dates (numeric) then try the array formula below.
I would also suggest that you don't mix dates and text "Begins in March" in the same column.
Enter with CTRL-SHIFT-ENTER.
Excel Workbook
ACADAEAFAGAHAIAJAKALAM
3DateDayTimeLocationTournamentPlayersCourseWednesdayFridaySaturday
4Friday, January 3, 2020Friday7amWilsonNew Year's Super Skins60Wilson267
5Saturday, January 11, 2020Saturday7amHardingLeaning Tree Classic60Harding356
6Wednesday, January 22, 2020Wednesday7amHarding3/6 Scramble32LA City Away000
7Saturday, February 8, 2020Saturday7amWilsonThe Bambino60Total
8Friday, February 21, 2020Friday7amWilson1457: Banned by Law60
9Begins in MarchFourball Championship96
10Saturday, March 7, 2020Saturday7amHardingThe Rogow72
11Wednesday, March 18, 2020Wednesday7amHardingSt. Patty's Shamble84
12Friday, March 27, 2020Friday7amWilsonArnie's Army60
13Saturday, April 4, 2020Saturday7amWilsonGPGC 36 Hole Championship Day 184
14Sunday, April 5, 2020Sunday7amHardingGPGC 36 Hole Championship Day 284
15Friday, April 24, 2020Friday7amHardingThe Didrikson32
16Saturday, May 2, 2020SaturdayIndian Canyons South8th Annual Palm Springs Desert Classic40
17Sunday, May 3, 2020SundayDesert Willow Firecliff8th Annual Palm Springs Desert Classic40
18Friday, May 15, 2020Friday7amHansen DamThe Dam60
19Saturday, May 30, 2020Saturday7amHardingMarty Tregnan Memorial84
Sheet
 
Upvote 0
Re: Need help with countifs formula

double click in the formula highlight this and press f9
(IFERROR(FIND(K$1,$B$2:$B$37),0)=1)
Check the result to see if it's an error, it should be a bunch of trues and falses(ctrl z to put the formal back in)

if there is no error then check this formula using the same method
($E$2:$E$37=$I2)

If you are getting an error in 1 of those, it's likely due to formatting
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top