Count rows where meets two conditions

[pau1]

Hi Guys,

I would really appreciate your help with a problem i havent been able to solve.

I have filebin.net/fs4xd6hv4ibamp86 attached an example doc. You can think of this as a hourly sales report vs weekday. I have a table with two columns First a date and time collum containing e.g. "22/06/2020 16:32:47". and a seccond showing sale amount. Each row is a transaction we have had.

I have a grid with hours going along the top, and day of week going down.

I have been able to get total sales for example when day = monday and hour = 4pm using

Please Login or Register  to view this content.

But i need to get quantity for orders that also meet both these criterias. I can get one using

Please Login or Register  to view this content.

but need to ensure its also

Please Login or Register  to view this content.

Ive tried by doing

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Thank you for taking the time to read this and reply,

Kindest Regards

Paul

[arthurbr]

Please post a sample sheet ( see yellow banner)

[Fluff13]

How about
=SUMPRODUCT((TEXT(Table1[Date],"dddd")=B7)*(TEXT(Table1[Date],"hh")="16"))

[pau1]

How about
=SUMPRODUCT((TEXT(Table1[Date],"dddd")=B7)*(TEXT(Table1[Date],"hh")="16"))

Perfect, Thank you

[Fluff13]

You're welcome & thanks for the feedback.

[pau1]

A couple of side questions id appreciate your help with for

Please Login or Register  to view this content.

1) How can i have instead of saying ="16" it refrences cell C5 which ideally would contain "16:00"

2) how do i lock the date collum, it doesnt seem to like adding $ in front like

Please Login or Register  to view this content.

Kindest Regards,

Paul

[Fluff13]

You can use
=SUMPRODUCT((TEXT(Table1[Date],"dddd")=B7)*(TEXT(Table1[Date],"hh")=TEXT(C5,"hh")))

One of the problems with tables, is anchoring the columns. I know it's possible, but can't remember how.

[pau1]

Thank You again, The solution was to Duplicate the column references as if referring to multiple columns.

E.g.

Please Login or Register  to view this content.