Sumif with 2 sets of criteria :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 Sumif with 2 sets of criteria

JRCBoston
Board Regular

Joined: 19 Sep 2003
Posts: 10

Flag:

Status: Offline

Sumif with 2 sets of criteria

Hi-

The sumif function only sums using one set of criteria, and I want to use more than one set of criteria. Does anyone have any suggestions? For instance, I want to be able to sum column D, if column A is equal to Yes and column b is equal to Monday. Or something like that.

Thanks.

Jon

Fri Sep 19, 2003 8:11 pm

.

Joined: 15 Feb 2002
Posts: 14083
Location: The Hague
Flag:

Status: Offline

Re: Sumif with 2 sets of criteria

=SUMPRODUCT((Arange="Yes")*(Brange="Monday"),Drange)

For more multiconditional counting & summing, see:

http://www.mrexcel.com/board2/viewtopic.php?t=59063

Fri Sep 19, 2003 8:20 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: Sumif with 2 sets of criteria

If you haven't already done so... you ought to add-in and use the Conditional Sum Wizard. It provides a step-by-step guide for the construction of conditional array formulas such as...

{=SUM(IF(range1="Yes",IF(range2="Monday",range3)))}

Why write the formula when Excel will do it for you?

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For

Fri Sep 19, 2003 8:25 pm

Pugster
Board Regular

Joined: 17 Nov 2003
Posts: 45
Location: What's it called?
Flag:

Status: Offline

Re: Sumif with 2 sets of criteria

I am trying to use the Conditional Sum wizard but am experiencing problems. I have a named array which has all the days of the month across the top, names of all staff down the first column, which department they work in in the second column and their shift start time is entered into the body of the table.

I have been able to use the Conditional sum wizard to display how many staff we have in each department for each day for each different start time for the dates between 10/11 and 30/11 but when I follow the exact same procedure for anything out with these dates the error message reads that I have a randionized value which will have to be removed.

I can not find these figures based on randonized values. I have deleted all the data except the date at the top of the column and I still cant get it to work!!

Any tips?
_________________
I didnt get where I am today by quoting 70's sitcoms.

Mon Nov 17, 2003 1:11 pm

Pugster
Board Regular

Joined: 17 Nov 2003
Posts: 45
Location: What's it called?
Flag:

Status: Offline

Re: Sumif with 2 sets of criteria

It must have had something to do with the way the dates were formatted. I have now fixed the problem by removing the dates, using the wizard and then hard typing the dates back in.
_________________
I didnt get where I am today by quoting 70's sitcoms.

Mon Nov 17, 2003 1:49 pm
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum