Need Some formula

[Penny Bangalore]

Hello,

the below is my data range as table, i want to know the sum of Review and Posted as per the date.. I know i can do with the pivot. but i need a formula to perform this task because i need this to connect this to different workbook with vlookup or index match.

please help

Excel 2010
	A	B	C	D
1	Date	Users	Review	Posted
2	5/21/2015	user1	24	244
3	5/21/2015	user2	11	225
4	5/21/2015	user3	48	226
5	5/21/2015	user4	34	244
6	5/21/2015	user5	48	204
7	5/21/2015	user6	23	218
8	5/21/2015	user7	41	211
9	5/21/2015	user8	17	240
10	5/21/2015	user9	39	217
11	5/21/2015	user10	19	214
12	5/21/2015	user11	47	248
13	5/22/2015	A	33	208
14	5/22/2015	b	27	245
15	5/22/2015	c	12	232
16	5/22/2015	A	47	223
17	5/22/2015	b	16	219
18	5/22/2015	c	39	216
19	5/22/2015	A	40	218
20	5/22/2015	b	48	213
21	5/22/2015	c	46	225
22	5/22/2015	A	45	203
23	5/22/2015	b	50	242
24	5/22/2015	c	20	231
25	5/22/2015	A	36	223
26	5/22/2015	b	36	245
27	5/22/2015	c	42	243
28	5/22/2015	A	22	214
29	5/22/2015	b	19	213
30	5/22/2015	c	27	217
Sheet2

 

[Mackers]

Hi Penny

I'm not exactly sure what your criteria are, but it sounds like you might do well to use a SUMIFS() statement.

Say if you wanted the sum of all of the Reviews on 21st May 2015, you could do:

=SUMIFS(C2:C30, A2:A30, DATE(2015,5,21))

You can change the DATE() part for A2 if you wanted to sum all the reviews with the same date as A2. If you wanted to sum the Posted, you should change the C2:C30 for D2:D30.

Hope that helps - let me know if I've got the wrong end of the stick.

Mackers

 

[SpillerBD]

Penny,
You could still use a Pivot Table as a source for a Vlookup or Index(Match..), but is more reliable to use GETPIVOTDATA
The real downside to Pivot Table is the Refresh Pivot Table has to be invoked manually (or by an Event handler.)
In other words, I agree the Pivot Table may not be right for this situation but not for the reasons you indicated.

 

[Penny Bangalore]

Hi Mackers,

thanks for the reply

i am ok with the formula you suggested , but the date i should change manually everyday, what do you say about this? please help..

 

[Penny Bangalore]

Hi spiller Thanks for the reply, however it seems that your suggestion is good .. i can refresh the pivot everyday no problem with that.. but am not good with getpivot formula, can you help me more on this please

 

[SpillerBD]

Hi spiller Thanks for the reply, however it seems that your suggestion is good .. i can refresh the pivot everyday no problem with that.. but am not good with getpivot formula, can you help me more on this please

I referred someone to this kb from Microsoft earlier: https://support.office.com/en-us/article/GETPIVOTDATA-function-8c083b99-a922-4ca0-af5e-3af55960761f?CorrelationId=4a2f9f14-41b8-43d3-ae07-0cab093f785f&ui=en-US&rs=en-US&ad=US
But normally when you try and create a formula that references a value in a Pivot Table (by selecting the cell) Excel will create the GETPIVOTDATA formula for you instead of the cell reference. That makes it easier to get the format and references right and then adjust for any variable you wish to apply.