Can I use two arguments for sumif?

[ayang_008]

Hi,

Can I use double arguments for sumif function?

I am trying to sum all the numbers if it satisfies two arguments. Can't get it to work...

[Ron Coderre]

We need to know more details about what you're trying to do.
Also, please post what you've tried....even if it didn't work.

[ayang_008]

Hi, I need help in summarizing a data table...
here is my data, I need to summarize the number of sales by product by each salesman...

Product Sales Salesman
101 67 John
102 84 John
103 26 John
101 48 Toni
105 13 Mike
108 45 Mike
109 0 Jane
101 84 John
102 0 Jane
105 26 Jane
108 78 John
108 26 John
102 58 Jane
101 67 Toni
106 95 John
109 0 John
107 12 Jane
101 35 John
102 18 Toni
103 45 Jane
104 28 Mike
105 34 Mike
102 15 Mike
101 15 Mike

here is the format of the result that I want...

I use sumif function to sum each product sold by each salesman which is sold in different days....but sumif function can't accept two criteria, can it?


Product John Mike Jane Toni
101
102
103
104
105
106
107
108
109

Any help will be appreciated...

Thanks

[oldchippy]

Hi,

With your second table in F1:J10

Product John Mike Jane Toni
101 186 15 0 115
102 84 15 58 18
103 26 0 45 0
104 0 28 0 0
105 0 47 26 0
106 95 0 0 0
107 0 0 12 0
108 104 45 0 0
109 0 0 0 0

G2=SUMPRODUCT(--($A$2:$A$25=$F2)*--($C$2:$C$25=G$1)*--($B$2:$B$25)) copy across to J2 and down to J10

[wviveen]

This also helped me !! Thanks buddy !

[Third Eye]

This helped me too! Thanks, oldchippy!

[tigertiger]

Hi,

With your second table in F1:J10

Product John Mike Jane Toni
101 186 15 0 115
102 84 15 58 18
103 26 0 45 0
104 0 28 0 0
105 0 47 26 0
106 95 0 0 0
107 0 0 12 0
108 104 45 0 0
109 0 0 0 0

G2=SUMPRODUCT(--($A$2:$A$25=$F2)*--($C$2:$C$25=G$1)*--($B$2:$B$25)) copy across to J2 and down to J10

or,

=SUMPRODUCT(($A$2:$A$25=$F2)*($C$2:$C$25=G$1),$B$2:$B$25) 

OR we can use SUMIFS in Excel 2007

=SUMIFS($B$2:$B$25,$A$2:$A$25,$F2,$C$2:$C$25,G$1) 

Syntax extract from help of Microsoft:

SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Sum_range is one or more cells to sum, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

Criteria_range1, criteria_range2, … are 1 to 127 ranges in which to evaluate the associated criteria.

Criteria1, criteria2, … are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.