SUMIF with the criteria range
 

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?

SUMIF with the criteria range
 

On Nov 13, 11:22*pm, Lado Kiknadze wrote:
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))

SUMIF with the criteria range
 

On Nov 14, 7:49*pm, Don Guillett Excel MVP
wrote:
On Nov 13, 11:22*pm, Lado Kiknadze wrote:

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

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.

SUMIF with the criteria range
 

Try

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

--

HTH

Bob

"Lado Kiknadze" wrote in message
...
On Nov 14, 7:49 pm, Don Guillett Excel MVP
wrote:
On Nov 13, 11:22 pm, Lado Kiknadze wrote:

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

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.

SUMIF with the criteria range
 

On Nov 15, 7:15*pm, "Bob Phillips" wrote:
Try

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

--

HTH

Bob

"Lado Kiknadze" wrote in message

...
On Nov 14, 7:49 pm, Don Guillett Excel MVP
wrote:





On Nov 13, 11:22 pm, Lado Kiknadze wrote:

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

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