Can you help me with a formula please

hassanpc · Mar 14, 2021

i am trying to add vat automatically to my suppliers . i have the same suppliers every months and i need to add vat to them automatically every time i import a statement from the bank into my excel sheet template
and don't want to do that job every week. if cell contains a text (suppliers list) then cell ( amount) * 20% and this should be applied to all suppliers, so if supplier is 0% vat then there will be none added automatically

thank you so much

example of suppliers list i get from bank

ljubo_gr · Mar 15, 2021

Maybe:
=IF( ISNUMBER (MATCH (BankStatement, NamedRangeOfSuppliers, 0)), Amount*(1+0.2), Amount)

hassanpc · Mar 15, 2021

will try thank you so much for replying

hassanpc · Mar 15, 2021

hassanpc said:
this is how my sheet is , will be helpful if explain more how to add the formula . B4 is the date Amount is D4 Memo is F4 Vat is G4 hope this help

ljubo_gr · Mar 15, 2021

Hi, Hassan

You need to have a unique list of suppliers(Memo col) as named range. Named Ranges in Excel
Always write formula from the middle, from inside. From middle to the ends.
You wrote that VAT is always 20%, if not, put formula in VAT col similar as in post #2.

Or.....
=IF( ISNUMBER( MATCH( Memo_Col , Supps!$A$1:INDEX(Supps!$A:$A,COUNTA(Supps!$A:$A)) , 0)) , Amount*(1+0.2), Amount)
Where Memo_col are suppliers from bank's statement, Supps is sheet name with all suppliers starting in a1 cell.
Replace these formula names as suits you. Memo is F4, F5...., VAT enter as 0.2 as general formatting and enter in formula where is *(1+G4) part.
As you can, see, i hope, the heart of your formulae is MATCH part. If that MATCH is found then do Amount times 20%, otherwise just Amount.
I hope this helps, i'm excel novice too.

English also ?

hassanpc · Mar 16, 2021

thank you so much for the help
i tried as much as i can , but its not happening with me. sorry if i am troubling you lot with this issue. i included the sheet that i am working on , may be it makes more sense if you see it .

Test Add VAt Automaticcaly to suppliers.xlsx

A

B

C

D

E

F

1

VAT January-2021

2

3

Date

Amount

Suppliers

VAT

Gross Inc VAT

4

5

1/4/21

($46.78)

STRIPE STRIPE-FWHJ8HENG3P DDR

6

1/4/21

($20.00)

Twitter Online Ads IRELAND

7

1/4/21

($9.20)

GOOGLE *GSUITE_tax IRELAND

8

1/4/21

($476.99)

AWS EMEA LUXEMBOURG

9

1/4/21

($216.58)

FACEBK R4SYXY2ZF2 IRELAND

10

1/4/21

($16.00)

FACEBK *LUFJQZENV2 IRELAND

11

1/5/21

($400.00)

207463 33159949 OPTIONAL FT

12

1/5/21

($35.20)

THOMAS POULSON 74816.131642.FAIL FT

13

1/5/21

($20.00)

DAVID MARCHAND 75429.SUBS.REF. FT

14

1/5/21

($30.00)

JOHN WALKER 71544.130623.FAIL FT

15

1/5/21

($0.01)

DAVID MARCHAND 75429 FT

16

1/5/21

($0.01)

THOMAS POULSON 74816 FT

17

1/5/21

($13.27)

STRIPE STRIPE-FWHJ8HENG3P DDR

18

1/6/21

($75.82)

TWILIO INC USA

19

1/6/21

($196.35)

STRIPE STRIPE-FWHJ8HENG3P DDR

20

1/6/21

($1.20)

123-REG WFU8171932 DDR

21

1/7/21

($57.68)

FRESHWORKS INC USA

22

1/7/21

($24.00)

Quickbooks ON 06 JAN BDC

23

1/7/21

($100.00)

H CHALF 74917.APP.SD. STO

24

1/7/21

($100.00)

PHILLIP FERRINGO 74015.APP.SD STO

25

1/7/21

($6.50)

********************** COMMISSION FOR

26

1/7/21

($55.71)

STRIPE STRIPE-FWHJ8HENG3P DDR

27

1/7/21

($747.63)

TAXIBUTLERSERVICE VDQ88MR DDR

28

1/7/21

($500.00)

207463 33159949 OPTIONAL FT

29

1/8/21

($12.44)

STRIPE STRIPE-FWHJ8HENG3P DDR

30

1/11/21

($100.00)

TAXIAPP HOLDINGS L ACC. OPENING FT

31

1/11/21

($2.40)

123-REG WFU8171932 DDR

32

1/11/21

($19.18)

STRIPE STRIPE-FWHJ8HENG3P DDR

33

1/11/21

($17.00)

FACEBK *GHDR3ZSNV2 IRELAND

34

1/11/21

($20.00)

LUKE MCCLUSKIE 79750.SUBS.REF. FT

35

1/11/21

($0.01)

LUKE MCCLUSKIE 79750 FT

36

1/11/21

($300.00)

SARAH CLEVELAND ADMIN.WE.08.01.21 FT

37

1/12/21

($7.99)

Microsoft*Microsof ON 12 JAN BDC

38

1/12/21

($16.53)

STRIPE STRIPE-FWHJ8HENG3P DDR

39

1/13/21

($116.47)

MailChimp USA

40

1/13/21

($20.39)

STRIPE STRIPE-FWHJ8HENG3P DDR

41

1/15/21

($500.00)

GOOGLE ADS11769816 IRELAND

42

1/15/21

($1.20)

123 REG LTD ON 14 JAN BDC

43

1/15/21

($5.99)

GOOGLE *Google Pla ON 14 JAN BDC

44

1/15/21

($20.84)

STRIPE STRIPE-FWHJ8HENG3P DDR

45

1/15/21

($360.00)

COOPERATIVES UK UCOAA8DBC7-D780 DDR

46

1/18/21

($300.00)

SARAH CLEVELAND ADMIN.WE.15.01.21 FT

47

1/18/21

($34.00)

GARY NEAGLE 60505.130866.FAIL FT

48

1/18/21

($20.00)

DARREN PULESTON 51254.SUBS.REF FT

49

1/18/21

($5.00)

MOHAMEDALI GULAMO 80316.133277.SCRUB FT

50

1/18/21

($1.75)

Microsoft US* AUSTRALIA

51

1/18/21

($20.63)

STRIPE STRIPE-FWHJ8HENG3P DDR

52

1/19/21

($10.40)

GRANT CABALLERO 80288.134505.FAIL FT

53

1/19/21

($25.00)

FACEBK *RFVRQYANV2 IRELAND

54

1/19/21

($10.00)

giffgaff ON 18 JAN BDC

55

1/19/21

($195.78)

STRIPE STRIPE-FWHJ8HENG3P DDR

56

1/20/21

($1.20)

123-REG WFU8171932 DDR

57

1/20/21

($9.69)

STRIPE STRIPE-FWHJ8HENG3P DDR

58

1/21/21

($2,000.00)

207463 33159949 OPTIONAL FT

59

1/21/21

($100.00)

H CHALF 74917.APP.SD. STO

60

1/21/21

($100.00)

PHILLIP FERRINGO 74015.APP.SD. STO

61

1/22/21

($300.00)

SARAH CLEVELAND ADMIN.WE.22.01.21 FT

62

1/22/21

($72.49)

STRIPE STRIPE-FWHJ8HENG3P DDR

63

1/25/21

$5.07

Stripe Payments UK STRIPE BGC

64

1/25/21

($20.80)

ANTHONY MOORE 67009.133772.FAIL FT

65

1/25/21

($89.99)

MCAFEE *WWW.MCAFEE IRELAND

66

1/25/21

($40.00)

JOHN PAUL HARRIS PHONE BURSARY STO

67

1/25/21

($8.00)

ID MOBILE LIMITED 12086222/001 DDR

68

1/26/21

($58.43)

VLS RE KLARNA BANK 435597010851624 DDR

69

1/26/21

($16.16)

STRIPE STRIPE-FWHJ8HENG3P DDR

70

1/27/21

($38.00)

TESCO STORE 2296 ON 26 JAN CLP

71

1/27/21

($40.00)

JAY NICOLA PHONE BURSARY STO

72

1/27/21

($23.40)

STRIPE STRIPE-FWHJ8HENG3P DDR

73

1/28/21

($35.20)

MOHAMED WARSAME 72836.133320.FAIL FT

74

1/28/21

($100.00)

207463 93423441 FT

75

1/28/21

($68.95)

GOOGLE *CLOUD_01F2 IRELAND

76

1/28/21

($32.40)

STRIPE STRIPE-FWHJ8HENG3P DDR

77

1/29/21

($14.32)

STRIPE STRIPE-FWHJ8HENG3P DDR

78

1/19/21

($360.00)

GOOGLE IRELAND GG102EWHFZ BGC

79

1/19/21

($300.00)

GARY JAMES BROUGHT Gary B FB Ads BGC

80

1/19/21

($34.00)

SARAH CLEVELAND ADMIN.WE.26.02.21 FT

81

1/20/21

($20.00)

DAVID MINCHIN 79592.TB.SD.02.21 FT

82

1/20/21

($5.00)

M J HARRIS STANDDOWN FT

83

1/21/21

($1.75)

SARAH CLEVELAND ADMIN.WE.05.03.21 FT

84

1/21/21

($20.63)

DAVID FORSTER 70537STANDDOWN FT

85

1/21/21

($10.40)

GOOGLE *CLOUD_01F2 IRELAND

86

1/22/21

($25.00)

LIQUID 11 LTD ON 28 FEB BDC

87

1/22/21

($10.00)

H CHALF 74917.APP.SD. STO

88

1/25/21

($195.78)

JAY NICOLA PHONE BURSARY STO

89

1/25/21

($1.20)

STRIPE STRIPE-FWHJ8HENG3P DDR

90

1/25/21

($9.69)

GOOGLE GSUITE_taxi IRELAND

91

1/25/21

($2,000.00)

OURDEBTFREELIVES.C USA

92

1/25/21

($100.00)

123-REG WFU8171932 DDR

93

1/26/21

($100.00)

STRIPE STRIPE-FWHJ8HENG3P DDR

94

1/26/21

($300.00)

STRIPE STRIPE-FWHJ8HENG3P DDR

95

1/27/21

($300.00)

ABC ACCOUNTANCY ON 03 MAR BDC

96

1/27/21

($300.00)

AWS EMEA LUXEMBOURG

97

1/27/21

($300.00)

TAXIBUTLERSERVICE VDQ88MR DDR

98

1/28/21

($300.00)

STRIPE STRIPE-FWHJ8HENG3P DDR

99

1/19/21

($300.00)

SARAH CLEVELAND ADMIN.WE.05.03.21 FT

100

1/19/21

($300.00)

PAUL GLYNN 67183.SUBS.REF. FT

101

1/19/21

($300.00)

Quickbooks ON 04 MAR BDC

102

1/20/21

($300.00)

STRIPE STRIPE-FWHJ8HENG3P DDR

103

1/20/21

($300.00)

CHARGES COMMISSION FOR

104

1/21/21

($300.00)

FRESHWORKS INC USA

105

1/21/21

($300.00)

PHILLIP FERRINGO 74015.APP.SD STO

106

1/21/21

($300.00)

STRIPE STRIPE-FWHJ8HENG3P DDR

107

1/22/21

($300.00)

STRIPE STRIPE-FWHJ8HENG3P DDR

108

1/22/21

($300.00)

TWILIO INC USA

109

1/25/21

($300.00)

GOOGLE ADS11769816 IRELAND

Monthly Statements try

ljubo_gr · Mar 16, 2021

From D column extract unique suppliers with pivot table, then from that table make list of supps. Then use formula in post #5 inside F column.

Can you help me with a formula please

hassanpc

New Member

Excel Facts

ljubo_gr

Board Regular

hassanpc

New Member

hassanpc

New Member

ljubo_gr

Board Regular

hassanpc

New Member

ljubo_gr

Board Regular

Similar threads

Forum statistics

Share this page

Can you help me with a formula please

hassanpc

New Member

Excel Facts

ljubo_gr

Board Regular

hassanpc

New Member

hassanpc

New Member

ljubo_gr

Board Regular

hassanpc

New Member

ljubo_gr

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock