SUM LOOKUP Results

[rustistic]

Hi everyone,

I have searched all forums and I can't find the answer to this question, at least not one I understand. I will try to make this as clear as possible, so bare with me and please ask if I need to explain something further.

I have multiple columns, in the first column "Date" the second "Name" and in the third column "Value Aus$".

Example:
Date | Name | Value Aus$
01/01/2010 | Tom | $100
05/03/2010 | Mark | $70
06/03/2010 | Sam | $60
06/03/2010 | Tom | $50
08/03/2010 | Sam | $40
09/03/2010 | Tom | $90


** There are hundreds of rows of data and multiple entries for each name.

What I need to be able to do is look up a "Name" that is in a cell and return the SUM all the corresponding "Value Aus$".

Example:
Tom | =(SUM OF ALL "TOM" VALUES)
Sam | =(SUM OF ALL "SAM" VALUES)

In the example above, the sum for Tom would be =$240 and for Sam =$100.


Thank you for your help in advance

 

[Phalon]

@Phalon

=SUMIFS(C:C,A:A,E1,B:B,F1)

where E1 = Tom and F1 1-Apr-2016.

Thanks Aladin!

One more question - is there a way to make this formula effective for a date range?

For example I am using CountIfs to return counts within a range using:

=COUNTIFS($A:$A,">="&$N$3,$A:$A,"<"&$N$3+1)

Where $A:$A = the submission date inclusive of timestamp eg 04/01/2016 13:42:37
$N$3 = the date I'm looking for

So if N3 = April 1st then it would return all relevant data from 04/01/2016 between 00:00:01 and 23:59:59 or so.

I could then tweak the "+1" to say "+6" to get the week starting from April 1st.

I hope that makes sense - completely self taught with excel and I'm sure I make it sound more complicated than it needs to be!

Cheers

 

[Aladin Akyurek]

You seem to have date/time values, not just date values.

=SUMPRODUCT(--(INT($A$2:$A$100)>=E1),--(INT($A$2:$A$100)<=F1))

would count all date/time records between E1 and F1. E1 and F1 are supposed to be date values, not date/time values.

 

[Phalon]

You seem to have date/time values, not just date values.

=SUMPRODUCT(--(INT($A$2:$A$100)>=E1),--(INT($A$2:$A$100)<=F1))

would count all date/time records between E1 and F1. E1 and F1 are supposed to be date values, not date/time values.

Thanks Aladin - I separated the ranges out into two different cells as above and everything now works fine. Thanks for your help.

 

[Aladin Akyurek]

Thanks Aladin - I separated the ranges out into two different cells as above and everything now works fine. Thanks for your help.

Great to hear that. You are welcome.