Using VLOOKUP to calculate the sum of specific dates

[Drewmyster]

I’m never great at explaining these things, but the simple requirement is to look up a date that matches the date in column A (A2 for example) and calculate the sum of all cells in column B (for example) for each row that relates to that date (located in column A on another sheet) which the formula has looked up.

How is this done please?

 

[etaf]

SUMIF()

=SUMIF( Sheet2!A2:A1000 , A2, Sheet2!B2:B1000)

=SUMIF ( The range to look for the date , the date to lookfor, the range to add up where it matches in the same row as the date )

 

[Joe4]

It sounds like maybe you can use a SUMIF or SUMIFS formula (you use these formulas to sum up values that meet a certain criteria).
See:

MS Excel: How to use the SUMIF Function (WS)

This Excel tutorial explains how to use the Excel SUMIF function with syntax and examples. The SUMIF function is a worksheet function that adds all numbers in a range of cells based on one criteria (for example, equal to 2000).

www.techonthenet.com

MS Excel: How to use the SUMIFS Function (WS)

This Excel tutorial explains how to use the Excel SUMIFS function with syntax and examples. The Microsoft Excel SUMIFS function adds all numbers in a range of cells, based on a single or multiple criteria.

www.techonthenet.com

If this not appear to do what you want, or you are having issues getting it to work, please post a sample of your data and expected results.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Drewmyster]

Thank you for this reply.

This has solved my question, but it has prompted a side one which you may be able to help with to complete my spreadsheet…

Having now achieved all the daily figures using the formula shared above, I now want to calculate the total sum of values for a month from daily figures.

Column A has the dates for October, column B has the sum value from each day.

Sheet2 has the months listed in column A and I want to input a formula in column B to total the sum of everything calculated in the original sheet for each day for the month of October.

Can someone please show me how this is done? I appreciate the support.

 

[etaf]

depending in how the data is laid out & if they are real dates
then a SUMIFS() with

And use the date as criteria
so
=SUMIFS(B1:B13,A1:A13,">="&"1/10/21",A1:A13,"<="&"31/10/21")
But you may want to code the dates based on the months in column A - but not sure how your data looks

Book1
	A	B	C	D
1	1-Oct-21	1
2	2-Oct-21	2		91
3	3-Oct-21	3
4	4-Oct-21	4
5	5-Oct-21	5
6	6-Oct-21	6
7	7-Oct-21	7
8	8-Oct-21	8
9	9-Oct-21	9
10	10-Oct-21	10
11	11-Oct-21	11
12	12-Oct-21	12
13	13-Oct-21	13
Sheet5

Cell Formulas
Range	Formula
D2	D2	=SUMIFS(B1:B13,A1:A13,">="&"1/10/21",A1:A13,"<="&"31/10/21")

OR
=SUMIFS(B1:B13,A1:A13,">="&D2,A1:A13,"<="&EOMONTH(D2,0))
where the date in D2 is 1st of month ie 1/10/21 and formatted to show just the month & year

Book1
	A	B	C	D	E
1	1-Oct-21	1
2	2-Oct-21	2		Oct/2021	55
3	3-Oct-21	3		Nov/2021	36
4	4-Oct-21	4
5	5-Oct-21	5
6	6-Oct-21	6
7	7-Oct-21	7
8	8-Oct-21	8
9	9-Oct-21	9
10	10-Oct-21	10
11	11-Nov-21	11
12	12-Nov-21	12
13	13-Nov-21	13
Sheet5

Cell Formulas
Range	Formula
E2:E3	E2	=SUMIFS(B1:B13,A1:A13,">="&D2,A1:A13,"<="&EOMONTH(D2,0))

 

[Drewmyster]

Hello again, sorry to revisit this thread after the helpful replies before…

I had been successfully using this formula last month to work out my figures;

=IF(SUMIF(A:A,L1,F;F)=0,"",SUMIF(A:A,L1,F:F))

This correctly returned the sum value of figures matching todays date (as shown in L1).

Now however, I have replaced numbers in column F with words “Yes” and “No”

I want the formula to count the number of cells in column F, which match the date in column A with L1, which return the word “Yes”

Please show me how to do this.

Thank You.

 

[Joe4]

Try using COUNTIF instead of SUMIF.
It is structured exactly the same. See: COUNTIF function

I will let you take a run at it, as it will be a good exercise to help make sure you have a firm understanding of how these formulas work.
Post back with your formula attempt if you run into issues.

 

[Drewmyster]

Try using COUNTIF instead of SUMIF.
It is structured exactly the same. See: COUNTIF function

I will let you take a run at it, as it will be a good exercise to help make sure you have a firm understanding of how these formulas work.
Post back with your formula attempt if you run into issues.

I understand the logic of what this formula does, but I can't work out how to add a criteria so it knows only to search the cells that have a match with the current date in L1.

Does the formula still need to begin with =IF(COUNTIF(..... or does it need to have something else.

 

[Joe4]

If you need to match on multiple fields (i.e. "Date" and "Yes"), then you can use COUNTIFS, which allow for multiple criteria.
See: COUNTIFS function