# How to replace the defined list in sumif(cell range,{defined list},cell range)?

1. ## How to replace the defined list in sumif(cell range,{defined list},cell range)?

Hi,

I am doing a spreadsheet to track my expenses by different category and under a category, I am trying to catch the details like "Dine In" will include breakfast, lunch, dinner and supper.
I'm trying to catch the details from a monthly calendar format and the formula I used for daily calculation is =SUM(SUMIF(B39:B44,{"breakfast","lunch","dinner","supper"},C39:C44))

This has created a big problem for me. If I were to add or remove an item, example "breakfast", I had to remove 365 times. Is there any way to replace the defined list in {} to just a "Dine In" list so that I do'nt have to change 365 times for the whole year?

2. ## Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

why do you wrap everything in a sum formula when you already doing sumif?

3. ## Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

1) Highlight the cells you want to update
2) Press CTRL-H to open the REPLACE wizard
3) IN the SEARCH box enter: {"breakfast",
4) In the REPLACE box, enter: {

Notice how we included the characters on each side of the string we wanted to update? Then we didn't need the comma any more, so it ended up just being the {

4. ## Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

Originally Posted by humdingaling
why do you wrap everything in a sum formula when you already doing sumif?
click on one of those formulas and then goto Formulas > Evaluate Formula, watch that formula unfold. Now do you understand? Without the SUM(), only the first string would activate. It's a non-CSE array.

5. ## Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

wrap the whole sumif with sumproduct and you can then used named ranges for your list of products

i did 3 days (in yellow) as examples

6. ## Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

Hi humdingaling,

Thanks this is what I am looking for! :-)
by the way, is there any limit for the named range? I know there is a limit of cells that we can put as a range (that's why I had so Jan1, Jan2, Jan3., etc), not sure how many more range I can define.

7. ## Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

dont think there would be in the way your using it

you should probably look into different way of calculating sumif instead of naming range every day though
your method is not very user friendly to update

9. ## Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

Originally Posted by humdingaling
dont think there would be in the way your using it

you should probably look into different way of calculating sumif instead of naming range every day though
your method is not very user friendly to update
Hmm....Any idea how it will be more user friendly? I wanted the calendar to stay with daily items input.

10. ## Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

i did something very basic without interfering to what you are doing already too much

i did rename your 1-31 to be 1st 2nd etc
reason for that is apparent when you look at the file

all i did was add some helper columns to the right which you can hide from plain view
then sumifs based on that column
seems to work perfectly with reference table sheet

11. ## Re: How to replace the defined list in sumif(cell range,{defined list},cell range)?

Ah....a bit complicated but I think it's better then selecting the 31 different ranges. Thanks!! :-)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1