Vlookup & sum

[Drewmyster]

Hi there,

I'm trying to do the following;

Lookup a range of cells that match a date and then produce the total sum of these cells as opposed to the first one that is located.

Please advise.

 

[jim may]

Not sure when the SumIfs() formula was introduced, but...

Excel Workbook
	A	B	C	D	E	F	G	H
1	Period	Week	Total	Proof		Period	3/1/2011	<< Input
2	1/31/2011	3	10			SUM	272
3	1/31/2011	3	15
4	1/31/2011	3	20	45
5	2/5/2011	3	45
6	2/5/2011	3	27	72
7	3/1/2011	3	98
8	3/1/2011	3	52
9	3/1/2011	3	78
10	3/1/2011	3	22
11	3/1/2011	3	22	272
Sheet1

Excel 2007

Cell Formulas
Range	Formula
G2	=SUMIFS(C:C,A:A,G1)
D4	=SUM(C2:C4)
D6	=C6+C5
D11	=SUM(C7:C11)

 

[Drewmyster]

Thanks but I still can't get that formula to work for what I'm trying to do.

Date range is Cell A1 on a "Totals" sheet
Total range is in Sheet1 and Sheet1 has both a date column and total column to look up against. There are multiple totals to add in rows C:C.
I want the formula to add the sum of all date matches.

Please advise.

Thanks

 

[Drewmyster]

Anyone else able to help with this?

 

[T. Valko]

Thanks but I still can't get that formula to work for what I'm trying to do.

Date range is Cell A1 on a "Totals" sheet
Total range is in Sheet1 and Sheet1 has both a date column and total column to look up against. There are multiple totals to add in rows C:C.
I want the formula to add the sum of all date matches.

Please advise.

Thanks

Anyone else able to help with this?

Your explanation is not very clear.

Try expressing your question something like this...

I want to sum values in the range C2:C100 if the date in the range A2:A100 = 1/1/2011.

It would be just about impossible to not understand that!

 

[Drewmyster]

I want to find the sum total of all cells that are in the date range of 25/05/2011 and that match cell B3's date (which I can change when I wish to).

I also want to count how many occurances of 25/05/2011 there are in that date range (A3:A650).

So far I've used VLOOKUP to do this, but it only returns the first known result in ascending order, but what I want is to get the sum total of all known results for that date range.

I hope this helps.

 

[T. Valko]

I want to find the sum total of all cells that are in the date range of 25/05/2011 and that match cell B3's date (which I can change when I wish to).

I also want to count how many occurances of 25/05/2011 there are in that date range (A3:A650).

So far I've used VLOOKUP to do this, but it only returns the first known result in ascending order, but what I want is to get the sum total of all known results for that date range.

I hope this helps.

OK, so the dates are in the range A3:A650. What is the range to sum?

Try this...

=SUMIF(A3:A650,B3,range_to_sum)

Replace "range_to_sum" with the actual range address.

To get the count of dates:

=COUNTIF(A3:A650,B3)

 

[Drewmyster]

Thanks T. Valko, that really helps alot thanks.

Just a spin-off question about the COUNTIF formula;

How can I use this formula to search for a text string against the date?

I've used this formula =COUNTIF('TEST'!A:J,"Enabled") to count the number of occurances of the word "Enabled" but if I ask it to look against the date in B3 it returns errors.

Please can you advise?

 

[T. Valko]

Thanks T. Valko, that really helps alot thanks.

Just a spin-off question about the COUNTIF formula;

How can I use this formula to search for a text string against the date?

I've used this formula =COUNTIF('TEST'!A:J,"Enabled") to count the number of occurances of the word "Enabled" but if I ask it to look against the date in B3 it returns errors.

Please can you advise?

Try something like this...

=SUMPRODUCT((date_range=B3)*(text_range="Enabled"))

Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007 or later. And, even if you are using Excel 2007 or later, you should avoid using entire columns as range references with SUMPRODUCT.

 

[Drewmyster]

The SUMPRODUCT formula doesn't seem to be working for me, I get an error message each time that says;

"Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated."

Please advise?