GOOGLE SHEETS: Sumproduct or something different?

J

jgruberman

New Member
Joined
Jan 15, 2024
Messages
16
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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!
 
Last edited by a moderator:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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
ABCDEFGHI
1
2count ifsumproduct
39:15blackhawks22
49:30
59:45
610:00
710:15blackhawks
88:00blackhawks
910:45
1011:00
Sheet1
Cell Formulas
RangeFormula
H3H3=COUNTIFS(E3:E10,"blackhawks",D3:D10,">"&TIMEVALUE("09:00:00"))+COUNTIFS(F3:F10,"blackhawks",D3:D10,">"&TIMEVALUE("09:00:00"))
I3I3=SUMPRODUCT((E3:F10="Blackhawks")*(D3:D10>TIMEVALUE("09:00:00")))
 
Upvote 0
Solution
etaf said:
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
ABCDEFGHI
1
2count ifsumproduct
39:15blackhawks22
49:30
59:45
610:00
710:15blackhawks
88:00blackhawks
910:45
1011:00
Sheet1
Cell Formulas
RangeFormula
H3H3=COUNTIFS(E3:E10,"blackhawks",D3:D10,">"&TIMEVALUE("09:00:00"))+COUNTIFS(F3:F10,"blackhawks",D3:D10,">"&TIMEVALUE("09:00:00"))
I3I3=SUMPRODUCT((E3:F10="Blackhawks")*(D3:D10>TIMEVALUE("09:00:00")))
Click to expand...
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!
 
Upvote 0
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
DEFGH
1
2Count
39:15blackhawks2
49:30
59:45
610:00
721:12blackhawks
822:00blackhawks
922:45
1023:00
11
12
Count
Cell Formulas
RangeFormula
H3H3=SUM(FILTER(--(E3:F1000="blackhawks"),D3:D1000>21/24))
 
Upvote 0
Peter_SSs said:
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
DEFGH
1
2Count
39:15blackhawks2
49:30
59:45
610:00
721:12blackhawks
822:00blackhawks
922:45
1023:00
11
12
Count
Cell Formulas
RangeFormula
H3H3=SUM(FILTER(--(E3:F1000="blackhawks"),D3:D1000>21/24))
Click to expand...
I'm actually using Google Sheets(don't ban me lol) and only have the amount of rows that I need on the sheet
 
Upvote 0
jgruberman said:
I'm actually using Google Sheets(don't ban me lol)
Click to expand...
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.

1705373447321.png
 
Upvote 0
Peter_SSs said:
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
Click to expand...
Noted. I did not realize there was another forum, else I would have followed the instructions. I saw " * Questions" and got tunnel vision. :)
 
Upvote 0
Fair enough. We also understand that you are new to the forum and it does take a while to learn your way around. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Google Sheet - Count Drop Downs, Total Columns, Check, Array - Errors
Replies
0
Views
213
jennifer7i884
J
N
  • Question
Google Sheet - Math formula inside a Google Sheet formula
Replies
0
Views
197
Naegino
N
S
  • Question
GOOGLE SHEETS: Data from other sheets that grows as each sheet grows
Replies
3
Views
259
Joe4
Joe4
T
  • Solved
Sum a dynamic sized range
Replies
7
Views
322
KRice
K
jl2509
  • Solved
Google Sheets Sumifs from multiple columns
Replies
7
Views
383
Sufiyan97
Sufiyan97

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top