UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Match & Count, Any Version    
 
   
tpitman
post May 27 2020, 02:41 PM
Post#1



Posts: 664
Joined: 20-June 04
From: UK


I have a Microsoft form with an underlying spreadsheet.

I have attached an example of the results of the spreadsheet.

I would like to count the number "ON" for each day. I'm thinking I need a combination of Count/Index/Match, but I need help starting me off if possible please
Go to the top of the page
 
June7
post May 27 2020, 03:26 PM
Post#2



Posts: 1,523
Joined: 25-January 16
From: The Great Land


Not seeing anything attached.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
tpitman
post May 27 2020, 03:39 PM
Post#3



Posts: 664
Joined: 20-June 04
From: UK


Sorry, attached now
Attached File(s)
Attached File  Book1.zip ( 12.81K )Number of downloads: 5
 
Go to the top of the page
 
June7
post May 27 2020, 04:02 PM
Post#4



Posts: 1,523
Joined: 25-January 16
From: The Great Land


This would be so simple with an aggregate query in Access.

By "ON" you mean all the 1's? Consider:

=SUMIF($A$3:$A$13,$G3,$B3:$B$13)


This post has been edited by June7: May 27 2020, 04:14 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
tpitman
post May 28 2020, 12:39 AM
Post#5



Posts: 664
Joined: 20-June 04
From: UK


Great Thanks!

However, I have been told the 1 & 0 have been changed to "IN" and "OUT"


Can I still use SUMIF or do I need to change it to COUNTIF, and where would I place the "=IN"


Go to the top of the page
 
tpitman
post May 28 2020, 11:38 AM
Post#6



Posts: 664
Joined: 20-June 04
From: UK


To explain it better, please see the attached

for each day, I need to count the number of people signing "IN"
Attached File(s)
Attached File  screenshot.jpg ( 69.28K )Number of downloads: 2
 
Go to the top of the page
 
June7
post May 28 2020, 01:55 PM
Post#7



Posts: 1,523
Joined: 25-January 16
From: The Great Land


Try:

=COUNTIFS($A$3:$A$12,$G3,$B$3:$B$12,"IN")

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
tpitman
post May 28 2020, 04:03 PM
Post#8



Posts: 664
Joined: 20-June 04
From: UK


Great thanks

However, although it works with my demo, the Forms spreadsheet containing the Start date seems to be the problem because of how the cell is formatted.

i.e.

Start Time 20/5/2020 10:37:38 (Generated by the Form)

Does not see 20/5/20 as being the same date

Go to the top of the page
 
June7
post May 28 2020, 04:30 PM
Post#9



Posts: 1,523
Joined: 25-January 16
From: The Great Land


Well, I use dates in U.S./Access standard so can't really help with international date. But formatting cells should not change actual value and calc still works (tested) if cell value is actually a number.

Time component certainly won't allow exact match. Have to extract date part.

Hit limit of my capability with Excel formulas.

And I would probably not even use Excel to begin with.

This post has been edited by June7: May 28 2020, 04:39 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    8th July 2020 - 02:55 AM