SUMIF with the criteria range

[Lado Kiknadze]

I have problem to use formula SUMIF(RANGE, CRITERIA, RANGE)
I want to summarize range according to criteria which is range.
In my case Criteria is range A1:A5
I know that i can write formula in this way: =SUMIF(RANGE, A1, RANGE)
+SUMIF(RANGE, A2, RANGE)+SUMIF(RANGE, A3, RANGE)+SUMIF(RANGE, A4,
RANGE)+SUMIF(RANGE, A5, RANGE)

Can i write this formula in a easier way? sometimes i have criteria
range long list and i want to write compact formula. Can you help me?

 

[Don Guillett Excel MVP]

I have problem to use formula SUMIF(RANGE, CRITERIA, RANGE)
I want to summarize range according to criteria which is range.
In my case Criteria is range A1:A5
I know that i can write formula in this way: =SUMIF(RANGE, A1, RANGE)
+SUMIF(RANGE, A2, RANGE)+SUMIF(RANGE, A3, RANGE)+SUMIF(RANGE, A4,
RANGE)+SUMIF(RANGE, A5, RANGE)

Can i write this formula in a easier way? sometimes i have criteria
range long list and i want to write compact formula. Can you help me?

where b1:b2 has your criteria. An ARRAY formula that must be entered
using ctrl+shift+enter


=SUM(IF(ISERROR(MATCH(A1:A8,B1:B2,0))=FALSE,C1:C8,0))

 

[Lado Kiknadze]

where b1:b2 has your criteria. An ARRAY formula that must be entered
using ctrl+shift+enter

=SUM(IF(ISERROR(MATCH(A1:A8,B1:B2,0))=FALSE,C1:C8,0))

Thank you so much! It works fine.
Now I discovered that I have one more criteria (condition). i want to
summarize same range with the same criteria in addition that cell
D1="c". Actually i have two criteria B1:B2 and D1="c"
Please let me know how i can compile formula in this case.

 

[Bob Phillips]

Try

=SUMPRODUCT(--(ISNUMBER(MATCH(M2:M20,A1:A5,0))),--(O2:O20="c"),N2:N20)

--

HTH

Bob

where b1:b2 has your criteria. An ARRAY formula that must be entered
using ctrl+shift+enter

=SUM(IF(ISERROR(MATCH(A1:A8,B1:B2,0))=FALSE,C1:C8,0))

Thank you so much! It works fine.
Now I discovered that I have one more criteria (condition). i want to
summarize same range with the same criteria in addition that cell
D1="c". Actually i have two criteria B1:B2 and D1="c"
Please let me know how i can compile formula in this case.

 

[Lado Kiknadze]

Try

=SUMPRODUCT(--(ISNUMBER(MATCH(M2:M20,A1:A5,0))),--(O2:O20="c"),N2:N20)

--

HTH

Bob






Thank you so much! It works fine.
Now I discovered that I have one more criteria (condition). i want to
summarize same range with the same criteria in addition that cell
D1="c". Actually i have two criteria B1:B2 and D1="c"
Please let me know how i can compile formula in this case.- Hide quoted text -

- Show quoted text -

Thank you Bob, it is great. I appriciate this.

Best wishes,
Lado