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

#### sheelamk

##### New Member
plz find the attached file

#### Attachments

• sample.xlsx
11.9 KB · Views: 6
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))

#### Attachments

• sample-ETAF.xlsx
12.6 KB · Views: 7
Last edited:
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

you are welcome

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 {}

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

#### Attachments

• sample (1).xlsx
9.1 KB · Views: 4

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

#### Attachments

• sample ETAF2.xlsx
12.7 KB · Views: 4
=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

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

=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.

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

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

#### Attachments

• sample ETAF3.xlsx
12.8 KB · Views: 2
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.