how to sum up a colum with multiple criteria in the same column

[sheelamk]

plz find the attached file

 

[ETAF]

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))

 

[sheelamk]

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))

thank you

 

[ETAF]

you are welcome

 

[sheelamk]

I want to add two more conditions from two different columns for sum in a particular column ...I have used sumifs but the formula is long..is there any other formula to calculate by using {}

 

[ETAF]

as i wrote
=SUM(SUMIF(C3:C29,{"ocr","efr","ocrd","efrd"},D3:D29))

is it SUMIFS() you need now

can you give the example/sample and i can then look i

still need to know the version of excel you are using

 

[sheelamk]

Please check the attachment

 

[pecoflyer]

@sheelamk : ETAF already requested your XL version more than once...

 

[ETAF]

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

 

[sheelamk]

=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

 

[ETAF]

no, hence why asked now many times what version of excel

 

[Debaser]

=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

Unless you have 2003 or older, that formula should work.

 

[sheelamk]

Above formula is working in office 365 but not in older version.even in Google sheets also it doesn't work

 

[ETAF]

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

 

[Debaser]

It does not need array entry as it uses an array constant. It will work in any version that supports SUMIFS, i.e. post 2003.