The limitation of SUMIFS?

[nickyowen99]

Hi all,

I have a question on multiple criteria for SUMIFS:

The criteria of the attached excel are:

If
a) it is Project 2
b) it is under "n"

the sum it, and the result is 7 then.

However, SUMIFS criteria should be on either row-directional or column directional, I guess it cannot be mixed. So in order to achieve this, should I use SUMIFS or anything else?

Thanks

Nicky


Thanks.

[benishiryo]

welcome to the forum, Nicky. can you explain how you got 7? if it's to sum up B5:K5, shouldn't it be 8? if i'm right:
=SUMIF(B3:K3,"n",INDEX(B4:K9,MATCH("Project2",A4:A9,0),))

the red portions can be referred to a cell instead.

ps: also noticed you posted another question which 2 of the members have answered. do give them feedback on whether it works or not. if it does, mark it as Solved.

[FDibbins]

Agree with Ben - how do you arrive at 7? We both get 8, and even a visual scan shows 8?

This will also work for you...
=SUMPRODUCT((A4:A9="Project2")*(B3:K3="n")*($B$4:$K$9))

[heshmaty]

welcome to the forum, Nicky. can you explain how you got 7? if it's to sum up B5:K5, shouldn't it be 8? if i'm right:
=SUMIF(B3:K3,"n",INDEX(B4:K9,MATCH("Project2",A4:A9,0),))

the red portions can be referred to a cell instead.

ps: also noticed you posted another question which 2 of the members have answered. do give them feedback on whether it works or not. if it does, mark it as Solved.

Dears
I have similar problem. I want to sum the cells which have a character in their rows AND a character in their columns. so I have to use wildcard.
SUMIFS function in combination with INDEX and MATCH functions only sums first column contents. I also used SUMPRODUCT.
This function don't accept wildcard, but accept full cell content. Here is two formulas I used:
=SUMIF(E3:E24,"*A*",INDEX(F3:N24,,MATCH("*H*",F2:N2,0)))
=SUMPRODUCT((E3:E24="*A*")*(F2:N2="*H*")*(F3:N24))
These two formulas don't work for me.

[JohnTopley]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[heshmaty]

Dears
I have similar problem. I want to sum the cells which have a character in their rows AND a character in their columns. so I have to use wildcard.
SUMIFS function in combination with INDEX and MATCH functions only sums first column contents. I also used SUMPRODUCT.
This function don't accept wildcard, but accept full cell content. Here is two formulas I used:
=SUMIF(E3:E24,"*A*",INDEX(F3:N24,,MATCH("*H*",F2:N2,0)))
=SUMPRODUCT((E3:E24="*A*")*(F2:N2="*H*")*(F3:N24))
These two formulas don't work for me.
The real data are attached. Any comments and suggestions are welcome.

[Glenn Kennedy]

I assume that you have misunderstood the request to open your OWN thread because English is not your first language. Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("A",E3:E24)))*(ISNUMBER(SEARCH("H",F2:N2)))*(F3:N24))

[heshmaty]

Thanks a lot Glenn. (sepas)

[Glenn Kennedy]

You're welcome. Next time, please start your own thread !!

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.