Sumif problem

[Siimo]

am I able to have more than 1 column for the range with just 1 criteria and 1 column for sum-range? been at this for hours trying to work this one out. I dont think the sumifs function is much use to me as i only have 1 criteria but i want it to find the criteria from different columns.

I tried using =SUMIF(A:E,R15,H:H) however this does not work

[Pepe Le Mokko]

Hi

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[mikeTRON]

Sumif only allows you to look for one criteria in one column. SumifS works for multiple criteria or multiple columns.

So now the question is if you want it to sum ONLY if it has this criteria met for all columns or ANY of the columns. As PePe has explained, post a sample workbook and I'm sure you will get a response fairly quickly with a solution.

[Siimo]

example doc.xlsx

Hope this is enough info

[FDibbins]

Those ranges dont exist in your data, and theu are text, not times/values, so you cant use > or < on them just as they are.
12:00-14:00
14:00-18:00

You will need to break the times into their own columns, or create a complext formula to break out the values in both the data and the sum ranges

[Siimo]

i hope this one is more help on how i tried to do it,

i made hidden cells to the left of the times to reference the times i needed if you understand what im gettin at

the only problem i had was, the reason the shifts are a drop down menu is due to shifts changing so based on what i choose in the drop down menu i wanted it to find the right column to reference what i needed to then take the data i needed

[Siimo]

if this is not possible to do this in the way I am trying does anybody have any other ideas on how I could get this data without having to put sum the formula manually as this is going to be a spreadsheet used on a daily basis in which the shifts will be different on a daily basis.

[FDibbins]

If you can modify your data to look likme this, it will make things a lot easier...

	M	N	O
20			Data
21	12:00		25
22	13:00		35
23	14:00		85
24	15:00		65
25	16:00		45
26	17:00		20
27	18:00		35
28
29
30	12:00	14:00	60
31	14:00	18:00	215
32	15:00	17:00	110

In O30, copied down, I used this...
=SUMIFS($O$21:$O$27,$M$21:$M$27,">="&M30,$M$21:$M$27,"<"&N30)

[Siimo]

This is absolutely perfect and will work great I just need to slightly change the worksheet round but it work fantastic.
Much Appreciated for your help.

[FDibbins]

happy to help and thanks for the feedback