Vlookup/sumproduct scan date and sumproduct adjecent cells

[charliew]

Hi,

I'm relatively new to excel so bare with me.

I'm trying to search a column for a specific date and then sumproduct in an adjacent cell...

this is what I've managed:

=SUMPRODUCT((O$2:O$963=ED)*(F$2:F$963=W69))

this doesn't work!

I have date ranges with times for completed reports (f) so sometimes 4 or 5 of the same dates with different times, and "ED" in some of the adjacent cells (o). I am trying to add up how many times ED is used for a each day...
I have a separate column with every date of the year in (for a different formula) (w) that I want to place this next to

Thank you for help in anticipation!

 

[Special-K99]

If you're comparing something to a value and that value is text you need to put the value in quotes

=SUMPRODUCT((O$2:O$963="ED")*(F$2:F$963=W69))

 

[AhoyNC]

Assuming your dates & time are actual Excel date/times and not text try:

SUMPRODUCT((O$2:O$963="ED")*(INT(F$2:F$963)=W69))

<colgroup><col width="439"></colgroup>

Dates and times are numeric in Excel so if your column F has both date & time and W69 is only the date you need to return the integer from the date/time (time being the decimal). See example below with date/time in column A and column B showing how it's stored in Excel.

Excel Workbook
	A	B
1	2/20/18 7:15 AM	43151.30208
2	2/20/18 8:30 AM	43151.35417
3
4	2/20/2018	43151
Sheet

 

[charliew]

Assuming your dates & time are actual Excel date/times and not text try:

SUMPRODUCT((O$2:O$963="ED")*(INT(F$2:F$963)=W69))

<tbody>
</tbody>

Dates and times are numeric in Excel so if your column F has both date & time and W69 is only the date you need to return the integer from the date/time (time being the decimal). See example below with date/time in column A and column B showing how it's stored in Excel.

	A	B
1	2/20/18 7:15 AM	43151.30208
2	2/20/18 8:30 AM	43151.35417
3
4	2/20/2018	43151

<colgroup><col style="width: 30px;"><col style="width: 130px;"><col style="width: 128px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

You beautiful people!! THANK YOU!! this makes total sense now!!

thanks again for your time!!!

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.

 

[charliew]

You're welcome. Thanks for the feedback and welcome to the forum.

good evening!

i tried to apply and tweak this formula to another part of the sheet but i cant get it chuck out the right number!

im trying to search column B for "Ext" and the cells that have Ext to then find the corresponding row and sumproduct the cells that have a "2" in them (for february)

this is what ive got:


=SUMPRODUCT((B$2:B$2000="Ext")*(AE2:AE2000="2"))

its chucking out 0...which is not correct!!

Where am i going wrong?...its feels fairly straight forward!

 

[AhoyNC]

Try:

=SUMPRODUCT((B$2:B$2000="Ext")*(MONTH(AE2:AE2000)=2)) <colgroup><col width="473"></colgroup><tbody> </tbody>

<colgroup><col width="473"></colgroup><tbody>
</tbody>

 

[charliew]

PERFECT...AGAIN!!

thank you...ok, so I have another one how can I make the below formulas return a blank cell?

=G544*24*60 if G is empty

=IF(B:B="Ext", "1","0") if B is empty

=IF(AP544>$AN$4, "1", "0") if ap is empty

thanks again and again!!

 

[AhoyNC]

When you put quotes around a number "1" Excel is going to return it as text and not as a number. Assuming you want to return it as a text then try:

=IF(G544="","",G544*24*60)

<colgroup><col width="314"></colgroup><tbody>
</tbody>

=IF(B:B="Ext","1","")

<colgroup><col width="314"></colgroup><tbody>
</tbody>

=IF(AP544="","",IF(AP544>$AN$4,"1","0"))

<colgroup><col width="314"></colgroup><tbody>
</tbody>

 

[charliew]

Again, worked perfectly!!

I would like to say there's one more but I'm sure it wont be!! I have managed to work out most other things on my own based on your advise - thank you! But I cant work out the next bit.

How can I get the below formula to give me a blank cell if there aren't any dates in the cells?

I am basically averaging turnaround times and if I drag the cell down it returns a 00:00:00 value - fair enough. But it scews my average data. The people that will be filling this data cant be trusted to drag the formulas where they need to be so it needs to work it out all automatically.

Any help, again, will be greatly appreciated.