excel formula help

JohnSwift10

This is probably not possible but I am trying to make an excel formula where if any of the cells in column C: contain the word shopping then the same cells in column are added together and displayed in a cell in column E:

=if C1orC2orC3orC4 contains shopping then add D1orD2orD3orD4 and so on.

so that if  only C2 and C3 contain shopping and C1 contains Halfords and C4 contains Baynes Baker it will only add D2 and D3 in cell E18.

Halfords and Baynes Baker are not eligible for cashback

This is to add all the cells that contain the cost of shopping to calculate the cashback I will be due on my Santander cashback current account.

I presently have something like

=1%*(D1772+D1769+D1771+D1773+D1774+D1776+D1775+D1785+D1786+D1780+D1782) in cell E18

Or is there another way to do this?


   A                          B                                           C                                               D                          E                        

Thu 15 Aug 2024		shopping	£75.70
Fri 16 Aug 2024		Standing Order
Tue 20 Aug 2024		shopping	£5.95
Thu 22 Aug 2024		shopping	£108.41
Sat 24 Aug 2024		petrol	£35.43
Sat 24 Aug 2024		shopping	£11.75
Tue 27 Aug 2024		Halfords	£25.00
Thu 29 Aug 2024		shopping	£100.00
Mon 02 Sep 2024			£251.20
Mon 02 Sep 2024			£54.50
Mon 02 Sep 2024			£53.31
Tue 03 Sep 2024		shopping
Wed 04 Sep 2024		Baynes Baker	£5.65
Thu 05 Sep 2024		shopping
Mon 09 Sep 2024		Gas&Electricity	£95.21
Tue 10 Sep 2024		shopping
Thu 12 Sep 2024		shopping	£100.00
Fri 13 Sep 2024	4 Direct Debit Payments at 1,00% Cashback			£4.54
Fri 13 Sep 2024	DEBIT CARD PAYMENTS AT 1,00% CASHBACK			£4.62

Hoenir

=IF(c2="Shopping", +d2,0)

Heedtheadvice

A great way is to use  a pivot table. Maybe need to do a little learning but very useful and flexible for all sorts of things. This might look a lot to read but bear with me as it is really worthwhile....

Give each column a heading ( good idea anyway rather than just column identity A,B,C etc ). Such as date, spend type, cost etc.

Highlight all the data including  the added headers and then use the menu system to select insert pivot.

After that add the 'cost' column name to the pivot value area, 'date' row to the pivot row area and 'spend ' to the column area.

That will add together each spend type by date ( not just shopping, you can filter that section). -assumption is that the table is defaulted to sum values but you can do other maths/calcs functions.

Now you may well think it is over complicated to do your simple task but pivot tables are so useful and versatile they are well worth learning plus you can automatically chart the results to see your spend trend at  glance!

Lots of ready made help on the web such as https://m.youtube.com/watch?v=Jx89DRlKe7E or Microsoft info just search "how to pivot excel" or similar

JohnSwift10

Hoenir said:

=IF(c2="Shopping", +d2,0)

How do I make it so that I can make it something like

 =IF(C2:C17="Shopping", +D2:D17,0)

as this gives #VALUE!

Vitor

=SUMIF(C:C, "*shopping*", D:D)

Explanation:

• C:C refers to the entire column C where you want to check for the word "shopping."
• "*shopping*" is the condition, where the asterisks (*) act as wildcards, allowing the formula to search for the word "shopping" anywhere within the text in each cell.
• D:D refers to the entire column D, which contains the values you want to sum when the condition is met.

Placement:

• Enter this formula in cell E1, and it will display the sum of all the values in column D where the corresponding cell in column C contains the word "shopping."

Heedtheadvice

Or....dare I add, use the automatic totals shown on the pivot table.....without the risk of a formula error!

beckstar1975

Vitor said:

=SUMIF(C:C, "*shopping*", D:D)

Explanation:

• C:C refers to the entire column C where you want to check for the word "shopping."
• "*shopping*" is the condition, where the asterisks (*) act as wildcards, allowing the formula to search for the word "shopping" anywhere within the text in each cell.
• D:D refers to the entire column D, which contains the values you want to sum when the condition is met.

Placement:

• Enter this formula in cell E1, and it will display the sum of all the values in column D where the corresponding cell in column C contains the word "shopping."

I use sumif/sumifs/countifs loads and didn’t know you could use wildcards, thanks so much!

JohnSwift10

Vitor said:

=SUMIF(C:C, "*shopping*", D:D)

Explanation:

• C:C refers to the entire column C where you want to check for the word "shopping."
• "*shopping*" is the condition, where the asterisks (*) act as wildcards, allowing the formula to search for the word "shopping" anywhere within the text in each cell.
• D:D refers to the entire column D, which contains the values you want to sum when the condition is met.

Placement:

• Enter this formula in cell E1, and it will display the sum of all the values in column D where the corresponding cell in column C contains the word "shopping."

How do I get it to only search cells such as between C25 and C35 when column C goes from C1 to C100 such that I can sum cells between a specific date?

double_dutchy

JohnSwift10 said:

How do I get it to only search cells such as between C25 and C35 when column C goes from C1 to C100 such that I can sum cells between a specific date?

Do you still need the "shopping" criterion too?

You can use SUMIFS function for summing with multiple criteria, e.g. if you have a start date in E1 and end date in E2 you can use this formula to sum column D when column C is "shopping" and column A is between those dates:

=SUMIFS(D:D,C:C, "Shopping",A:A,">="&E1,A:A,"<="&E2)

You can add more criteria as required

JohnSwift10

double_dutchy said:

JohnSwift10 said:

How do I get it to only search cells such as between C25 and C35 when column C goes from C1 to C100 such that I can sum cells between a specific date?

Do you still need the "shopping" criterion too?

You can use SUMIFS function for summing with multiple criteria, e.g. if you have a start date in E1 and end date in E2 you can use this formula to sum column D when column C is "shopping" and column A is between those dates:

=SUMIFS(D:D,C:C, "Shopping",A:A,">="&E1,A:A,"<="&E2)

You can add more criteria as required

Thanks, that works OK though how could I check for shopping and Petrol?
I can of course replace petrol with shopping as I normally only buy .petrol once a month.

I tried =SUMIFS(D:D,C:C, "Shopping"+"petrol",A:A,">="&E1,A:A,"<="&E2) which doesn't work

Heedtheadvice

Use a pivot table!!

That gives you lots of options that you keep on adding. Everything you have asked for to date....

This is a bit like a voice in the wilderness....