Sumifs with or

[srikandi2000]

[SOLVED]
Dear all,

Can anyone suggest on how best to solve this problem.

Situation: I need to use sumif(s) base on a validation list which contain "State"; "Private"; "State & Private". It is not a problem with only option "State" or "Private", but how do you make up for "State & Private" in which the condition can actually be just excluded.

Thanks in advance

 

[Xiq]

Can you give a sample file with an example of the expected result?

 

[NARAYANK991]

Hi ,

Do you mean to say that when a cell in that column contains the data State & Private , it should be included whether you have selected State or Private as the criterion ?

Narayan

 

[NARAYANK991]

Hi ,

You can use SUMIF(S) with the criteria having the wildcard character :

=SUMIF(criteria_range,"*" & criterion_cell_address & "*",sum_range)

Thus , suppose your data is such that :

criteria_range : A2:A20
sum_range : B2:B20
criterion_cell_address : C1

Then , if C1 contains State , the SUMIF will also include State & Private ; the same applies if the criterion is Private.

Narayan

 

[srikandi2000]

I am not sure if I understand. Are you saying that I have to create 2 cell for the result? I attach sample workbook.

Thank you

 

[NARAYANK991]

Hi ,

Is this acceptable ?

Narayan

 

[Xiq]

Try this:

=SUMIFS(C2:C12,A2:A12,H1,B2:B12,IF(H2="State+Private","*",H2))

 

[srikandi2000]

thanks Narayank, took me several minutes to understand it....