CountIfs Formula help

S

Sunshine8790

Board Regular
Joined
Jun 1, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
I have a sheet (I cannot share due to privacy) with 2 tabs.
The first tab has 10 columns:
Name, Shift, Contest Shift, Contest Day, Building, Supervisor, #Votes for Scariest, #Votes for Most Original, #Votes for Funniest, Outfit
There are 50+ Rows of data with the info of participants and corresponding info other than the #votes cells.

The 2nd tab has 4 columns:
Voter name, Vote for (participant name), Shift Day, Category

I currently have this formula in cell G2 (Votes for Scariest on first participant), which was a pain to create because I also had to manually type in each name on all 3 cells on row 2 for that person as well as change "Scariest" to "funniest" or "Most original" in the other columns, and repeat that process for each row for the different participants.
Bear in mind, some participated more than one day so their names come up more than once.
Excel Formula: 
=COUNTIFS(Votes!$B:$B,"Jane Doe",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Scariest")
(Real name in formula changed to Jane Doe)

Currently the formula I have is not working properly. It's showing me some results - but not all. Can someone please help me fix the one I have or help me come up with another?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Example Fake Participant Name tab:
Costume Contest Entrants and Votes.xlsx
ABCDEFGHIJ
1Participant NameShiftContest ShiftContest DayBuildingSupervisorVotes for ScariestVotes for Most OriginalVotes for FunniestOutfit
2Jane DoeD58D410ATuesday DayGFCremoved000Red Riding Hood
3John DoeD58D410ATuesday DayGFCremoved000Big Bad Wolf
4Fake Name 1D410AD410ATuesday DayGFCremoved000Scarecrow
5Fake Name 2D410AD410ATuesday DayGFCremoved000Mage
6Fake Name 3D410AD410ATuesday DayGFCremoved000Nun
7Fake Name 4E410AE410ATuesday NightGFCremoved000Funny Halloween shirt
8Fake Name 5E410AE410ATuesday NightGFCremoved000Funny Halloween shirt
9John DoeE410AE410AThursday DayGFCremoved000Black Cat
10Fake Name 3E410AE410AThursday DayGFCremoved000Ghost outfit
Sheet1
Cell Formulas
RangeFormula
G2G2=COUNTIFS(Votes!$B:$B,"Jane Doe",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Scariest")
H2H2=COUNTIFS(Votes!$B:$B,"Jane Doe",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Most Original")
I2I2=COUNTIFS(Votes!$B:$B,"Jane Doe",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Funniest")
G3G3=COUNTIFS(Votes!$B:$B,"John Doe",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Scariest")
H3H3=COUNTIFS(Votes!$B:$B,"John Doe",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Most Original")
I3I3=COUNTIFS(Votes!$B:$B,"John Doe",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Funniest")
G4G4=COUNTIFS(Votes!$B:$B,"Fake Name 1",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Scariest")
H4H4=COUNTIFS(Votes!$B:$B,"Fake Name 1",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Most Original")
I4I4=COUNTIFS(Votes!$B:$B,"Fake Name 1",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Funniest")
G5G5=COUNTIFS(Votes!$B:$B,"Fake Name 2",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Scariest")
H5H5=COUNTIFS(Votes!$B:$B,"Fake Name 2",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Most Original")
I5I5=COUNTIFS(Votes!$B:$B,"Fake Name2",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Funniest")
G6G6=COUNTIFS(Votes!$B:$B,"Fake Name 3",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Scariest")
H6H6=COUNTIFS(Votes!$B:$B,"Fake Name 3",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Most Original")
I6I6=COUNTIFS(Votes!$B:$B,"Fake Name 3",Votes!$C:$C,"Tuesday Day",Votes!$D:$D,"Funniest")
G7G7=COUNTIFS(Votes!$B:$B,"Fake Name 4",Votes!$C:$C,"Tuesday Night",Votes!$D:$D,"Scariest")
H7H7=COUNTIFS(Votes!$B:$B,"Fake Name 4",Votes!$C:$C,"Tuesday Night",Votes!$D:$D,"Most Original")
I7I7=COUNTIFS(Votes!$B:$B,"Fake Name 4",Votes!$C:$C,"Tuesday Night",Votes!$D:$D,"Funniest")
G8G8=COUNTIFS(Votes!$B:$B,"Fake Name 5",Votes!$C:$C,"Tuesday Night",Votes!$D:$D,"Scariest")
H8H8=COUNTIFS(Votes!$B:$B,"Fake Name 5",Votes!$C:$C,"Tuesday Night",Votes!$D:$D,"Most Original")
I8I8=COUNTIFS(Votes!$B:$B,"Fake Name 5",Votes!$C:$C,"Tuesday Night",Votes!$D:$D,"Funniest")
G9G9=COUNTIFS(Votes!$B:$B,"John Doe",Votes!$C:$C,"Tuesday Night",Votes!$D:$D,"Scariest")
H9H9=COUNTIFS(Votes!$B:$B,"John Doe",Votes!$C:$C,"Tuesday Night",Votes!$D:$D,"Most Original")
I9I9=COUNTIFS(Votes!$B:$B,"John Doe",Votes!$C:$C,"Tuesday Night",Votes!$D:$D,"Funniest")
G10G10=COUNTIFS(Votes!$B:$B,"Fake Name 3",Votes!$C:$C,"Tuesday Night",Votes!$D:$D,"Scariest")
H10H10=COUNTIFS(Votes!$B:$B,"Fake Name 3",Votes!$C:$C,"Tuesday Night",Votes!$D:$D,"Most Original")
I10I10=COUNTIFS(Votes!$B:$B,"Fake Name 3",Votes!$C:$C,"Tuesday Night",Votes!$D:$D,"Funniest")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:A10Cell ValueduplicatestextNO
G2:I10Cell Value>0textNO


Example Fake Voter Tab:
Costume Contest Entrants and Votes.xlsx
ABCD
1Voter NameVote forDayCategory
2Voter 1John DoeTuesday DayMost Original
3Voter 1Fake Name 1Tuesday DayFunniest
4Voter 2John DoeThursday Day Most Original
5Voter 3John DoeThursday Day Scariest
6Voter 4Fake Name 5Thursday NightScariest
Sheet2
 
Upvote 0
How about in G2 copied across & down
Excel Formula: 
=COUNTIFS(Votes!$B:$B,$A2,Votes!$C:$C,$D2,Votes!$D:$D,TEXTAFTER(G$1,"for "))
 
Upvote 0
Solution
Fluff said:
How about in G2 copied across & down
Excel Formula: 
=COUNTIFS(Votes!$B:$B,$A2,Votes!$C:$C,$D2,Votes!$D:$D,TEXTAFTER(G$1,"for "))
Click to expand...
Still not showing all results.
I have one for instance that has a vote for Funniest and Most original on Tuesday, and the same person with a vote for Funniest on Thursday but the formula is only showing the result for the one funny vote on Tuesday.
 
Upvote 0
That's because you have a criteria looking at the day/shift.
 
Upvote 0
Sunshine8790 said:
Still not showing all results.
I have one for instance that has a vote for Funniest and Most original on Tuesday, and the same person with a vote for Funniest on Thursday but the formula is only showing the result for the one funny vote on Tuesday.
Click to expand...
Your Thursday on the sheet Votes has a space on the end. Remove the space.
 
Upvote 0
@Fluff and @Alex Blakenburg
Thank you both - I'm still confused. (Just having one of those days). Where is there a space at the end?
I copied Fluff's formula and pasted it across and down so it replaced all my original formulas - What criteria is still wrong?
 
Upvote 0
20231101 Countifs Sunshine8790.xlsx
ABCDEF
1Voter NameVote forDayCategoryLength
2Voter 1John DoeTuesday DayMost Original
3Voter 1Fake Name 1Tuesday DayFunniest
4Voter 2John DoeThursday Day Most Original
5Voter 3John DoeThursday Day Scariest13
6Voter 4Fake Name 5Thursday NightScariest
7
8Manually typed inThursday Day12
Votes
Cell Formulas
RangeFormula
F5,F8F5=LEN(C5)
 
Upvote 0
I think I misunderstood what you were saying.
As Alex mentioned, on your sample data cells C3 & C4 have a space after the word "Day"
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
formula to VBA at value
Replies
5
Views
207
rjmdc
R
R
  • Solved
correct formula when cell is blank to leave blank
Replies
4
Views
342
rjmdc
R
Ramballah
  • Question
Huge nested IF or is there a simpler way?
Replies
4
Views
201
Ramballah
Ramballah
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
248
Insurance Dave
I
O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
448
KRice
K

Forum statistics

Threads
1,215,073
Messages
6,122,970
Members
449,095
Latest member
Mr Hughes

Share this page

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