nested if with a sum ,or sumifs help needed please

Wimpie

Wimpie

Board Regular
Joined
Aug 12, 2008
Messages
210
Good day

Ineed helpwith the below please
If ColumnV is 2 and ColumnQ is equal to Q63 add Column AB, if not give me AB63 in Column AE.
Line 196 and 237 works perfect how ever lines 63, 135 an 177 should give me R10 170,00 in stead of R16 995,00.
It is also adding line 196 and 237 which it scould not

The formula I am using is

=IF(V63=2;SUMIF(Q:Q;Q63;AB:AB);AB63)
 
Last edited by a moderator:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
[TABLE="width: 872"]
<colgroup><col width="64" style="width: 48pt;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" span="2"> <col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="60" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <col width="64" style="width: 48pt;"> <col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"]P[/TD]
[TD="width: 75, bgcolor: transparent"]Q[/TD]
[TD="width: 54, bgcolor: transparent"]R[/TD]
[TD="width: 55, bgcolor: transparent"]S[/TD]
[TD="width: 75, bgcolor: transparent"]T[/TD]
[TD="width: 60, bgcolor: transparent"]U[/TD]
[TD="width: 61, bgcolor: transparent"]V[/TD]
[TD="width: 92, bgcolor: transparent"]W[/TD]
[TD="width: 75, bgcolor: transparent"]X[/TD]
[TD="width: 84, bgcolor: transparent"]Y[/TD]
[TD="width: 75, bgcolor: transparent"]Z[/TD]
[TD="width: 52, bgcolor: transparent"]AA[/TD]
[TD="width: 64, bgcolor: transparent"]AB[/TD]
[TD="width: 70, bgcolor: transparent"]AC[/TD]
[TD="width: 61, bgcolor: transparent"]AD[/TD]
[TD="width: 71, bgcolor: transparent"]AE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: #005641"]Date From[/TD]
[TD="width: 75, bgcolor: #005641"]Date To[/TD]
[TD="width: 54, bgcolor: #005641"]Batch Ex[/TD]
[TD="width: 55, bgcolor: #005641"]Trans Ex[/TD]
[TD="width: 75, bgcolor: #005641"]Release Date[/TD]
[TD="width: 60, bgcolor: #005641"]Post Paid[/TD]
[TD="width: 61, bgcolor: #005641"]Nedbank(2) or DashPay(3)[/TD]
[TD="width: 92, bgcolor: #005641"]Merchant number & Amount[/TD]
[TD="width: 75, bgcolor: #005641"]Date For Column W[/TD]
[TD="width: 84, bgcolor: #005641"]Merchant number & Batch[/TD]
[TD="width: 75, bgcolor: #005641"]Date For ColumnY[/TD]
[TD="width: 52, bgcolor: #005641"]Batch Number[/TD]
[TD="width: 64, bgcolor: #005641"]Amount[/TD]
[TD="width: 70, bgcolor: #005641"]Bank Statement Date[/TD]
[TD="width: 61, bgcolor: #005641"]Day of the week[/TD]
[TD="width: 71, bgcolor: #005641"]Expected Statement Amount[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]63[/TD]
[TD="bgcolor: transparent, align: right"]2018/11/01[/TD]
[TD="bgcolor: transparent, align: right"]2018/11/02[/TD]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent, align: right"]1900/01/01[/TD]
[TD="bgcolor: transparent"]N[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]261666216995[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]26166620311[/TD]
[TD="bgcolor: transparent, align: right"]2018/11/02[/TD]
[TD="bgcolor: transparent, align: right"]810[/TD]
[TD="bgcolor: transparent, align: right"]R2 970,00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]R16 995,00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]135[/TD]
[TD="bgcolor: transparent, align: right"]2018/11/01[/TD]
[TD="bgcolor: transparent, align: right"]2018/11/02[/TD]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent, align: right"]1900/01/01[/TD]
[TD="bgcolor: transparent"]N[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]261666216995[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]26166620311[/TD]
[TD="bgcolor: transparent, align: right"]2018/11/02[/TD]
[TD="bgcolor: transparent, align: right"]578[/TD]
[TD="bgcolor: transparent, align: right"]R6 245,00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]R16 995,00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]177[/TD]
[TD="bgcolor: transparent, align: right"]2018/10/31[/TD]
[TD="bgcolor: transparent, align: right"]2018/11/02[/TD]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent, align: right"]1900/01/01[/TD]
[TD="bgcolor: transparent"]N[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]261666216995[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]26166620311[/TD]
[TD="bgcolor: transparent, align: right"]2018/11/02[/TD]
[TD="bgcolor: transparent, align: right"]338[/TD]
[TD="bgcolor: transparent, align: right"]R955,00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]R16 995,00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]196[/TD]
[TD="bgcolor: transparent, align: right"]2018/11/01[/TD]
[TD="bgcolor: transparent, align: right"]2018/11/02[/TD]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent, align: right"]1900/01/01[/TD]
[TD="bgcolor: transparent"]N[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]1035492275[/TD]
[TD="bgcolor: transparent, align: right"]2018/11/02[/TD]
[TD="bgcolor: transparent"]1035497[/TD]
[TD="bgcolor: transparent, align: right"]2018/11/02[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]R2 275,00[/TD]
[TD="bgcolor: transparent, align: right"]02-Nov-18[/TD]
[TD="bgcolor: transparent"]Fri[/TD]
[TD="bgcolor: transparent, align: right"]R2 275,00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]237[/TD]
[TD="bgcolor: transparent, align: right"]2018/10/24[/TD]
[TD="bgcolor: transparent, align: right"]2018/11/02[/TD]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent, align: right"]1900/01/01[/TD]
[TD="bgcolor: transparent"]N[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]1071424550[/TD]
[TD="bgcolor: transparent, align: right"]2018/11/03[/TD]
[TD="bgcolor: transparent"]1071421[/TD]
[TD="bgcolor: transparent, align: right"]2018/11/03[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]R4 550,00[/TD]
[TD="bgcolor: transparent, align: right"]03-Nov-18[/TD]
[TD="bgcolor: transparent"]Sat[/TD]
[TD="bgcolor: transparent, align: right"]R4 550,00
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
try
=IF(V63=2,SUMIFS(AB:AB,Q:Q,Q63,V:V,2),AB63)
 
Upvote 0
@ Fluff
Brilliant thank you, this resolves the issue 100%
Thank youso much, you are a life saver
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,974
Messages
6,175,737
Members
452,667
Latest member
vanessavalentino83

Share this page

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?

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
Back
Top