# Search across and sum down

#### wannabe12

##### New Member
Hi there
I have a journal where I have P1, P2 and so forth to P12 going across.
For each P* I have values for the next 10 rows i.e. values going down. Please see mini table below -
P1 is for Jan, P2 for Feb and so forth
When doing this I need to put a check in that checks the total for P1 or P*.
So I need a formula that searches for P* and when it finds it going across it totals up all the values in that column
For example for P2 the formula should return - 20,000
Thanks

 P1 P2 P3 P4 15000​ 5000​ 15000​ 25000​ 25000​ 5000​ 80000​ 30000​ 40000​ 10000​ 95000​ 55000​

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
MrExcelPlayground5.xlsx
ABCDEFGHIJKL
1P1P2P3P4P5P6P7P8P9P10P11P12
21500050001500025000165005500165002750038500495006050071500
3250005000800003000027500550088000170500253000335500418000500500
440000100009500055000440001100010450060500467500621500775500929500
551667116671433336666756833128331576677333368200090750011330001358500
6641671416718333381667705831558320166789833896500119350014905001787500
776667166672233339666784333183332456671063331111000147950018480002216500
8891671916726333311166798083210832896671228331325500176550022055002645500
910166721667303333126667111833238333336671393331540000205150025630003074500
1011416724167343333141667125583265833776671558331754500233750029205003503500
1112666726667383333156667139333293334216671723331969000262350032780003932500
12
13Which P?
145774583.3
Sheet18
Cell Formulas
RangeFormula
B14B14=SUM(INDEX(A2:L11,,A14))

How about
+Fluff 1.xlsm
ABCDEFG
1P1P2P3P4
21500050001500025000P220000
32500050008000030000
440000100009500055000
5
6
Main
Cell Formulas
RangeFormula
G2G2=SUM(INDEX(A2:D12,0,MATCH(F2,A1:D1,0)))

Another option

Book2
ABCDEFGH
1P1P2P3P4
21500050001500025000P4110000
32500050008000030000
440000100009500055000
5
6
Sheet2
Cell Formulas
RangeFormula
G2G2=SUMPRODUCT((A1:D1=F2)*A2:D4)

Thanks all

Glad we could help & thanks for the feedback.

Replies
10
Views
450
Replies
0
Views
264
Replies
1
Views
287
Replies
3
Views
204
Replies
1
Views
369

Threads
1,202,977
Messages
6,052,890
Members
444,608
Latest member
Krunal_Shah

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

### Disable AdBlock

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