Formula Help

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,795
Office Version
  1. 2010
Platform
  1. Windows
Here is a sample of my data, "Monday" will appear more than once in Column B but there will only be one Monday with "3" in Column A... How would I return the "01:05:03" to G2 please?

Excel Workbook
ABCDEFG
1HelperDayDuratonHelperDayDuration
29Saturday00:57:473Monday
38Sunday00:58:42
43Monday01:05:03
59Tuesday00:56:28
Sheet1
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
Hi How_Do_I

Does this do what you want (in Cell G2)?

Code:
=SUMPRODUCT(--($A$2:$A$5=$E$2),--($B$2:$B$5=$F$2),$C$2:$C$5)

Cheers

pvr928
 

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,795
Office Version
  1. 2010
Platform
  1. Windows
It certainly does thank you pvr928...! Thanks again.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,383
Office Version
  1. 2016
Platform
  1. Windows
Hi,

A simple SUMIFS will work also:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Helper</td><td style=";">Day</td><td style=";">Duraton</td><td style="text-align: right;;"></td><td style=";">Helper</td><td style=";">Day</td><td style=";">Duration</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">9</td><td style=";">Saturday</td><td style="text-align: right;;">0:57:47</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style=";">Monday</td><td style="text-align: right;;">1:05:03</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">8</td><td style=";">Sunday</td><td style="text-align: right;;">0:58:42</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">3</td><td style=";">Monday</td><td style="text-align: right;;">1:05:03</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">9</td><td style=";">Tuesday</td><td style="text-align: right;;">0:56:28</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet17</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=SUMIFS(<font color="Blue">C2:C5,A2:A5,E2,B2:B5,F2</font>)</td></tr></tbody></table></td></tr></table><br />
 

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,795
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Thanks jtakw
 

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,795
Office Version
  1. 2010
Platform
  1. Windows
My next question is, how would I find the row that "Monday" in Column B and "3" in column A appear in please?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,383
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks jtakw

You're welcome.

Do you mean Conditional Formatting to have the resultant Cell/Row highlighted?
 

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,795
Office Version
  1. 2010
Platform
  1. Windows
No jtakw, the actual row which I think I've got. I changed the OP to add Named Ranges:

=SUMPRODUCT(--(Helper=E2),--(Day=F2),ROW(Duraton))
 

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
Try (entered as an array formula (SHIFT + CTRL + ENTER)) in cell I2:

Code:
=SMALL(IF(($E$2=$A$2:$A$5)*($F$2=$B$2:$B$5),ROW($A$2:$A$5),""),1)

Cheers

pvr928
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

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
Top