# Need Some formula

#### Penny Bangalore

##### Board Regular
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.

Excel 2010
ABCD
1DateUsersReviewPosted
25/21/2015user124244
35/21/2015user211225
45/21/2015user348226
55/21/2015user434244
65/21/2015user548204
75/21/2015user623218
85/21/2015user741211
95/21/2015user817240
105/21/2015user939217
115/21/2015user1019214
125/21/2015user1147248
135/22/2015A33208
145/22/2015b27245
155/22/2015c12232
165/22/2015A47223
175/22/2015b16219
185/22/2015c39216
195/22/2015A40218
205/22/2015b48213
215/22/2015c46225
225/22/2015A45203
235/22/2015b50242
245/22/2015c20231
255/22/2015A36223
265/22/2015b36245
275/22/2015c42243
285/22/2015A22214
295/22/2015b19213
305/22/2015c27217
Sheet2

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
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

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.

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..

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

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.

Replies
10
Views
782
Replies
4
Views
3K
Replies
0
Views
544
Replies
10
Views
1K
Replies
12
Views
1K

1,196,248
Messages
6,014,223
Members
441,808
Latest member
xplainer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back