Home

Formula Problem Countif based on Unique value and based on another comlum

%3CLINGO-SUB%20id%3D%22lingo-sub-282537%22%20slang%3D%22en-US%22%3EFormula%20Problem%20Countif%20based%20on%20Unique%20value%20and%20based%20on%20another%20comlum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-282537%22%20slang%3D%22en-US%22%3E%3CP%3EI%20will%20first%20off%20say%20I%20am%20a%20travel%20agent%20to%20give%20you%20some%20context%20on%20what%20I%20use%20this%20information%20for.%20I%20have%20basic%20knowledge%20in%20Excel%20and%20it%20has%20all%20been%20self%20taught.%20For%20this%20spreadsheet%20my%20Microsoft%20Flow%20will%20input%20data%20into%20this%20spreadsheet%20and%20the%20counter%20at%20the%20top%20will%20keep%20track%20of%20how%20many%20seats%2Frooms%20I%20have%20sold%20vs%20unsold%20for%20group%20bookings.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*For%20the%20number%20of%20people%20I%20think%20I%20have%20it%20figured%20out.%20The%20only%20correction%20I%20would%20try%20and%20make%20is%20counting%20adult%20seats.%26nbsp%3B%20My%20way%20works%20but%20I%20dont%20think%20this%20is%20the%20best%20way.%20Ideally%20I%20would%20like%20to%20count%20adults%20it%20counting%20the%20number%20of%20people%20that%20do%20not%20have%20a%20value%20in%20the%20Child's%20Age%20Column.%20If%20that%20cant%20be%20fixed%20then%20that's%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20am%20using%20right%20now%20is%20just%20Minus-ing%26nbsp%3Bthe%20amount%20of%20teens%2Fkids%2Finfants.%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-right%22%20style%3D%22width%3A%20642px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59069iC687A40904A4B223%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screenshot_9.png%22%20title%3D%22Screenshot_9.png%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EHow%20I%20Calculate%20Adults.%3C%2FSPAN%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3Eds%2Finfants%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENow%20Onto%20my%20bigger%20issue%20and%20the%20main%20reason%20why%20I%20am%20posting.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ECounting%20the%20number%20rooms%20is%20more%20difficult.%20Each%20room%20is%20generated%20an%20ID%20through%20Microsoft%20Forms%2FFlow.%20This%20is%20their%20%22room%20number%22.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59071i38553C5288845AE3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screenshot_13.png%22%20title%3D%22Screenshot_13.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20For%20L5%20I%20would%20like%20it%20to%20be%20total%20of%20Junior%20Suite%20Rooms.%20From%20what%20I%20am%20thinking%20it%20has%20to%20count%20the%20unique%20values%20(room%20number)%20from%20a14%3A114%20and%20it%20has%20to%20match%20it%20with%20what%20rooms%20(k14%3Ak114)%26nbsp%3B%20that%20are%20labeled%20as%20Junior%20suites.%26nbsp%3B%20I%20believe%20I%20have%20use%20a%20countif%20or%20sumif%20but%20all%20the%20different%20ways%20wont%20work%20for%20me.%20This%20would%20%3D%20the%20amount%20of%20Junior%20Suites%20I%20have%20sold%3C%2FP%3E%3CP%3E(Keep%20in%20mind%20I%20dont%20just%20want%20to%20count%20all%20the%20Junior%20Suites(K14%3Ak114)%20since%20sometimes%20there%20is%20anywhere%20from%201-4%20people%20in%20a%20room)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Now%20After%20that%20I%20want%20to%20calculate%20L6%2C%20L7%2C%20L8%2C%20L9%2CL10%2C%20etc.%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20want%20it%20to%20do%20is%20calculate%20the%20unique%20values%20from%20Room%20Number%20(a14%3Aa114)%2Bmatch%20the%20rooms%20labeled%20as%20Junior%20Suites%20(K14%3Ak114)%2B%20match%20it%20with%20the%20Departure%20city%20(H14%3A114)(Regina%2C%20Saskatoon%2C%20etc.)%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20would%20then%20%3D%20the%20amount%20of%20Junior%20Suites%20I%20have%20sold%20from%20Regina%20(or%20Saskatoon%2C%20etc)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETotal%20Number%20of%20rooms%20is%20a%20number%20I%20put%20in%20(no%20formulas).%20Then%20once%20I%20have%20the%20above%20two%20situations%20figured%20out%20%22unsold%22%20as%20a%20simple%20formula.%20For%20example%20%22total%22-%22sold%22%3D%20unsold.%26nbsp%3B%20This%20is%20something%20I%20know%20how%20to%20do.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!!%20Between%20this%20and%20Microsoft%20Flow%20this%20is%20going%20to%20save%20me%20so%20much%20time%20and%20I%20am%20super%20excited!%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%26nbsp%3B%3C%2FP%3E%3CP%3ESamantha%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-282537%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormula%20help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Samantha Finnie
Regular Visitor

I will first off say I am a travel agent to give you some context on what I use this information for. I have basic knowledge in Excel and it has all been self taught. For this spreadsheet my Microsoft Flow will input data into this spreadsheet and the counter at the top will keep track of how many seats/rooms I have sold vs unsold for group bookings. 

 

 

*For the number of people I think I have it figured out. The only correction I would try and make is counting adult seats.  My way works but I dont think this is the best way. Ideally I would like to count adults it counting the number of people that do not have a value in the Child's Age Column. If that cant be fixed then that's fine.

 

What I am using right now is just Minus-ing the amount of teens/kids/infants. Screenshot_9.pngHow I Calculate Adults.

ds/infants

 

 

 

 

 

 

 

 

 

 

Now Onto my bigger issue and the main reason why I am posting.

Counting the number rooms is more difficult. Each room is generated an ID through Microsoft Forms/Flow. This is their "room number".

Screenshot_13.png

 

1. For L5 I would like it to be total of Junior Suite Rooms. From what I am thinking it has to count the unique values (room number) from a14:114 and it has to match it with what rooms (k14:k114)  that are labeled as Junior suites.  I believe I have use a countif or sumif but all the different ways wont work for me. This would = the amount of Junior Suites I have sold

(Keep in mind I dont just want to count all the Junior Suites(K14:k114) since sometimes there is anywhere from 1-4 people in a room)

 

2. Now After that I want to calculate L6, L7, L8, L9,L10, etc. 

What I want it to do is calculate the unique values from Room Number (a14:a114)+match the rooms labeled as Junior Suites (K14:k114)+ match it with the Departure city (H14:114)(Regina, Saskatoon, etc.) 

This would then = the amount of Junior Suites I have sold from Regina (or Saskatoon, etc)

 

Total Number of rooms is a number I put in (no formulas). Then once I have the above two situations figured out "unsold" as a simple formula. For example "total"-"sold"= unsold.  This is something I know how to do. 

 

Thank you in advance!! Between this and Microsoft Flow this is going to save me so much time and I am super excited! 

Thank you, 

Samantha 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies