You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

- Thread starter sheelamk
- Start date

you could use a number of sumif() + sumif()

=SUMIF(C3:C29,"ocr",D3:D29)+ SUMIF(C3:C29,"efr",D3:D29) +SUMIF(C3:C29,"ocrd",D3:D29)+SUMIF(C3:C29,"efrd",D3:D29)

format the cell as [H]:MM to see over 24hours time

ocr,efr,ocrd,efrd

OR if you have a later version of excel with the FILTER() function

=SUM(FILTER(D3:D29,(C3:C29="ocr")+ (C3:C29="efr") +(C3:C29="ocrd") +(C3:C29="efrd")))

and also there is a way to use {} for the criteria - =SUM(SUMIF(C3:C29,{"ocr","efr","ocrd","efrd"},D3:D29))

=SUMIF(C3:C29,"ocr",D3:D29)+ SUMIF(C3:C29,"efr",D3:D29) +SUMIF(C3:C29,"ocrd",D3:D29)+SUMIF(C3:C29,"efrd",D3:D29)

format the cell as [H]:MM to see over 24hours time

ocr,efr,ocrd,efrd

OR if you have a later version of excel with the FILTER() function

=SUM(FILTER(D3:D29,(C3:C29="ocr")+ (C3:C29="efr") +(C3:C29="ocrd") +(C3:C29="efrd")))

and also there is a way to use {} for the criteria - =SUM(SUMIF(C3:C29,{"ocr","efr","ocrd","efrd"},D3:D29))

Last edited:

thank youyou could use a number of sumif() + sumif()

=SUMIF(C3:C29,"ocr",D3:D29)+ SUMIF(C3:C29,"efr",D3:D29) +SUMIF(C3:C29,"ocrd",D3:D29)+SUMIF(C3:C29,"efrd",D3:D29)

format the cell as [H]:MM to see over 24hours time

ocr,efr,ocrd,efrd

OR if you have a later version of excel with the FILTER() function

=SUM(FILTER(D3:D29,(C3:C29="ocr")+ (C3:C29="efr") +(C3:C29="ocrd") +(C3:C29="efrd")))

and also there is a way to use {} for the criteria - =SUM(SUMIF(C3:C29,{"ocr","efr","ocrd","efrd"},D3:D29))

please provide the version , I suspect its older then 365 as the last sample had { and thats not needed in 365 or 2021 versions

Plus you have not said which of the 3 formulas i provided worked

Column A is NOT dates in sample just a number so I have used a Number

=SUM(SUMIFS(D3:D29,C3:C29,{"ocr","efr","ocrd","efrd"},A3:A29,1,B3:B29,">="&TIMEVALUE("06:00:00"),B3:B29,"<="&TIMEVALUE("18:00:00")))

for any further replies I need the version

and a sample with real dates in if thats what you want

Also please ask the question within the text of the forum , having to open a spreadsheet to see what you want , is not what most members would do - so you are limiting your possible replies

Plus you have not said which of the 3 formulas i provided worked

Column A is NOT dates in sample just a number so I have used a Number

=SUM(SUMIFS(D3:D29,C3:C29,{"ocr","efr","ocrd","efrd"},A3:A29,1,B3:B29,">="&TIMEVALUE("06:00:00"),B3:B29,"<="&TIMEVALUE("18:00:00")))

for any further replies I need the version

and a sample with real dates in if thats what you want

Also please ask the question within the text of the forum , having to open a spreadsheet to see what you want , is not what most members would do - so you are limiting your possible replies

Unless you have 2003 or older, that formula should work.=SUM(SUMIFS(D3:D29,C3:C29,{"ocr","efr","ocrd","efrd"},A3:A29,1,B3:B29,">="&TIMEVALUE("06:00:00"),B3:B29,"<="&TIMEVALUE("18:00:00"))) this is not working in older versions of excel

as mentioned it should work in older versions

but may need to be an array entry

control+shift+enter to get

{} around the formula

i dont have an older version to test

also google sheets is different and has less functions , so would not expect to work

Hence all the questions about versions etc needed

sumproduct may be an alternative

something like

=SUMPRODUCT((C3:C29={"ocr","efr","ocrd","efrd"}) *(A3:A29=1)*(B3:B29>=TIMEVALUE("06:00:00"))*(B3:B29<=TIMEVALUE("18:00:00"))*(D3:D29))

Sorry , BUT I dont answer google sheets , as i dont now the product very well , and its quite different

but may need to be an array entry

control+shift+enter to get

{} around the formula

i dont have an older version to test

also google sheets is different and has less functions , so would not expect to work

Hence all the questions about versions etc needed

sumproduct may be an alternative

something like

=SUMPRODUCT((C3:C29={"ocr","efr","ocrd","efrd"}) *(A3:A29=1)*(B3:B29>=TIMEVALUE("06:00:00"))*(B3:B29<=TIMEVALUE("18:00:00"))*(D3:D29))

Sorry , BUT I dont answer google sheets , as i dont now the product very well , and its quite different