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?

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

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 {

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.

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

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

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

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