GOOGLE SHEETS: Sumproduct or something different?

[jgruberman]

I have a list of teams in columns E and F. Sometimes a team name (e.g. Blackhawks) will appear in column E and sometimes it will appear in column F, multiple times in multiple rows.

If "Blackhawks" appears in Column E or F, I need to check Column D of that same row and count how many times in both columns E and F, the time shown in Column D is later than 9:00pm.

Is this a SUMPRODUCT formula or something else? If I could get some help figuring out a formula, would appreciate the help!

 

[etaf]

how about 2 countifs
=COUNTIFS(E3:E10,"blackhawks",D3:D10,">"&TIMEVALUE("09:00:00"))+COUNTIFS(F3:F10,"blackhawks",D3:D10,">"&TIMEVALUE("09:00:00"))

or
=SUMPRODUCT((E3:F10="Blackhawks")*(D3:D10>TIMEVALUE("09:00:00")))

Book1
	A	B	C	D	E	F	G	H	I
1
2								count if	sumproduct
3				9:15	blackhawks			2	2
4				9:30
5				9:45
6				10:00
7				10:15		blackhawks
8				8:00	blackhawks
9				10:45
10				11:00
Sheet1

Cell Formulas
Range	Formula
H3	H3	=COUNTIFS(E3:E10,"blackhawks",D3:D10,">"&TIMEVALUE("09:00:00"))+COUNTIFS(F3:F10,"blackhawks",D3:D10,">"&TIMEVALUE("09:00:00"))
I3	I3	=SUMPRODUCT((E3:F10="Blackhawks")*(D3:D10>TIMEVALUE("09:00:00")))

 

[jgruberman]

how about 2 countifs
=COUNTIFS(E3:E10,"blackhawks",D3:D10,">"&TIMEVALUE("09:00:00"))+COUNTIFS(F3:F10,"blackhawks",D3:D10,">"&TIMEVALUE("09:00:00"))

or
=SUMPRODUCT((E3:F10="Blackhawks")*(D3:D10>TIMEVALUE("09:00:00")))

Book1
	A	B	C	D	E	F	G	H	I
1
2								count if	sumproduct
3				9:15	blackhawks			2	2
4				9:30
5				9:45
6				10:00
7				10:15		blackhawks
8				8:00	blackhawks
9				10:45
10				11:00
Sheet1

Cell Formulas
Range	Formula
H3	H3	=COUNTIFS(E3:E10,"blackhawks",D3:D10,">"&TIMEVALUE("09:00:00"))+COUNTIFS(F3:F10,"blackhawks",D3:D10,">"&TIMEVALUE("09:00:00"))
I3	I3	=SUMPRODUCT((E3:F10="Blackhawks")*(D3:D10>TIMEVALUE("09:00:00")))

Thank you - I was able to accomplish this with a few small tweaks to the SUMPRODUCT, but cosmetic like changing the array to be E:F and the timevalue I put in 24H since the times I had listed were formatted as such. Thanks again!

 

[Peter_SSs]

SUMPRODUCT can be a bit resource-hungry if the range is large, like whole columns E:F. Another option that you could try would be the one below. I still would not use whole column references, just make the ranges plenty big enough to hold any amount of data that you are likely to have. If you do stick with SUMPRODUCT I would suggest that you still reduce the range. Why have Excel calculate over 1 million rows if you are only using vastly less than that?

24 01 16.xlsm
	D	E	F	G	H
1
2					Count
3	9:15	blackhawks			2
4	9:30
5	9:45
6	10:00
7	21:12		blackhawks
8	22:00	blackhawks
9	22:45
10	23:00
11
12
Count

Cell Formulas
Range	Formula
H3	H3	=SUM(FILTER(--(E3:F1000="blackhawks"),D3:D1000>21/24))

 

[jgruberman]

SUMPRODUCT can be a bit resource-hungry if the range is large, like whole columns E:F. Another option that you could try would be the one below. I still would not use whole column references, just make the ranges plenty big enough to hold any amount of data that you are likely to have. If you do stick with SUMPRODUCT I would suggest that you still reduce the range. Why have Excel calculate over 1 million rows if you are only using vastly less than that?

24 01 16.xlsm
	D	E	F	G	H
1
2					Count
3	9:15	blackhawks			2
4	9:30
5	9:45
6	10:00
7	21:12		blackhawks
8	22:00	blackhawks
9	22:45
10	23:00
11
12
Count

Cell Formulas
Range	Formula
H3	H3	=SUM(FILTER(--(E3:F1000="blackhawks"),D3:D1000>21/24))

I'm actually using Google Sheets(don't ban me lol) and only have the amount of rows that I need on the sheet

 

[Peter_SSs]

I'm actually using Google Sheets(don't ban me lol)

We won't ban you, but ask that in future you do not ask Google Sheets questions in the Excel Questions forum.

Rather, ask them in the location requested on the main forum page. I have moved this thread for you on this occasion.

 

[jgruberman]

We won't ban you, but ask that in future you do not ask Google Sheets questions in the Excel Questions forum.

Rather, ask them in the location requested on the main forum page. I have moved this thread for you on this occasion.

View attachment 105102

Noted. I did not realize there was another forum, else I would have followed the instructions. I saw " * Questions" and got tunnel vision.

 

[Peter_SSs]

Fair enough. We also understand that you are new to the forum and it does take a while to learn your way around.