Diasplay values based on date

[jtakajr]

Hello there - I'm new to this, but catch on pretty quickly.

I was hoping someone could help me solve a problem I've been working with.

At the end of each shift, leaders report out on their # of customers via Microsoft Form.

There are two shifts each day, AM and PM. So there are two form submissions one from the AM leader, and one from the PM leader. I'd like to be able to list the date, and what the AM & PM numbers were right next to each other so they are easier to view.

Any ideas on how one may accomplish this task? I have attached an example of what I would like to see.

[Sam Capricci]

I would try this in your desired customer count display (cell B3)
=SUMIFS('Form Submissions'!C:C,'Form Submissions'!$A:$A,'Desired Customer Count Display'!$A3,'Form Submissions'!$B:$B,'Desired Customer Count Display'!B$2)
then drag to C3 and drag down.

[jtakajr]

That does help! However, is there anyway to reference an exact date?

For example, sometimes the form is resubmitted for revisions. In that case there would be an additional row of information for the same shift previously submitted. While I'm aware this would probably cause an error, I'd prefer an error listed for that day rather than the entire sheet being compromised. Does that make any sense? I'm not sure if that's even possible.

[Sam Capricci]

That does help! However, is there anyway to reference an exact date?

this section of the formula is referencing the exact date, or at least the date you had in A3
'Form Submissions'!$A:$A,'Desired Customer Count Display'!$A3
it can also look like this...
'Form Submissions'!$A:$A,$A3
sometimes excel puts the tab name in the formula but it isn't needed for the formula unless the formula points to a different tab.
so if you put that formula in the desired customer count display tab it could also look like this and work...
=SUMIFS('Form Submissions'!C:C,'Form Submissions'!$A:$A,$A3,'Form Submissions'!$B:$B,B$2)

[jtakajr]

This is 100% exactly what I've been looking for, it works perfectly! Thank you so so much! Now I just need to figure out how it works so I can apply in other ways. Thank you so much!

[jtakajr]

How about this - what would be the best way to go about adding a third column's value? (revised document attached).

[Sam Capricci]

just change it to this...
=SUMIFS('Form Submissions'!C:C,'Form Submissions'!$A:$A,$A3)
drag down and to the right.
this will also work...
=SUMIF('Form Submissions'!$A:$A,$A3,'Form Submissions'!C:C)

[jtakajr]

So I'm really struggling for some reason. Will this not work if the response from the form are automatically formatted as a table? I can't seem to figure out the problem as to why it won't work.

[Sam Capricci]

because your values in the form submission table are text and not numbers. if you put in numbers (change the fields from text to number) and use this in B3 on census it should work fine for you.
=SUMIF('Form Submissions'!$A:$A,$A3,'Form Submissions'!H:H)