SUMIF FORMULA 1

[Scott02]

Hello all. I've tried a couple of ways and can't get this to work. I'm hoping someone out there has the answer.

I tried to use a SUMIF statement to add data when the range meets multiple criteria. Here's what I currently have:

=SUMIF(E7:E180,AND("> 6300034","< 6300501"),G7:G180)

where column E contains account numbers and column G contains amounts. I'm trying to write the formula to sum expenses for different ranges of account numbers. Any suggestions would be appreciated, but I'm not proficient with VBA if I can avoid it. As always, thanks in advance for your time and input.

Scott

 

[mbarron]

You may be better served using SUMPRODUCT

=SUMPRODUCT((E7:E180>6300034)*(E7:E18<6300501)*(G7:G180))


Mike

 

[kjv1611]

Have you tried it like this?

=SUMIF(E7:E180<>"",AND("> 6300034","< 6300501"),G7:G180)


Stephen         [infinity]
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[Scott02]

Thanks guys. I appreciate the quick responses.

The sumproduct worked. Now I'll have to read up on it to understand how.

Stephen, thanks for trying, but Excel wouldn't accept your formula.

Have a great day everyone.

Scott

 

[mbarron]

Or if you really like using SUMIF

=SUMIF(E7:E180,">6300034",G7:G180)-SUMIF(E7:E180,">=6300501",G7:G180)


Mike

 

[Scott02]

Another good alternative that worked. Thanks again.

Scott

 

[byundt]

You're not going to find this use of SUMPRODUCT documented in the on-line Excel help. It's really a "trick" that is widely known in sites like Tek-Tips. Here's my canned explanation of what's happening:

SUMPRODUCT formulas are very good for counting or summing just those situations where more than one criterion must be satisfied. Each of the criteria must be enclosed within parentheses, but you can have as many as you like--as long as they refer to the same number of rows. For example, to count the number of bachelors between 25 and 30 at a computer dating bureau:
=SUMPRODUCT((B1:B100="male")*(C1:C100>=25)*(C1:C100<=30))
And if their annual salary is in column D, the average income of this group of guys is:
=SUMPRODUCT((B1:B100="male")*(C1:C100>=25)*(C1:C100<=30)*D1100)/SUMPRODUCT((B1:B100="male")*(C1:C100>=25)*(C1:C100<=30))
You will note that each criteria is enclosed within parentheses, but the field being summed (column D) does not have to be.

The expressions inside the parentheses are converted to Boolean arrays of TRUE and FALSE values. Because TRUE may also be interpreted as 1 and FALSE as 0, SUMPRODUCT multiplies corresponding elements in each of the parenthetical expressions. That makes each one of those expressions a condition that must be satisfied simultaneously. Microsoft doesn't document this use of SUMPRODUCT function to count instances or add values when multiple criteria must be satisfied--but it works very well and is widely used at sites like Experts-Exchange.

 

[KenWright]

The SUMPRODUCT formula exploits the fact that Excel interprets TRUE as 1 and FALSE as 0. Take the formula below:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A")*(C9:C20))

This sets up an array that gives you something that looks like this
(depending on the variables of course):-

       A              B           C
 9   TRUE    *     FALSE    *     3
10   FALSE   *     FALSE    *     4
11   TRUE    *     TRUE     *     2
12   TRUE    *     TRUE     *     1
13   TRUE    *     FALSE    *     4
14   TRUE    *     TRUE     *     3
15   TRUE    *     TRUE     *     2
16   FALSE   *     TRUE     *     8
17   TRUE    *     TRUE     *     6
18   TRUE    *     TRUE     *     8
19   TRUE    *     TRUE     *     7
20   TRUE    *     TRUE     *     6

Which because TRUE=1 and FALSE=0, is interpreted as:-

      A            B             C
 9      1     *     0      *      3    =    0
10      0     *     0      *      4    =    0
11      1     *     1      *      2    =    2
12      1     *     1      *      1    =    1
13      1     *     0      *      4    =    0
14      1     *     1      *      3    =    3
15      1     *     1      *      2    =    2
16      0     *     1      *      8    =    0
17      1     *     1      *      6    =    6
18      1     *     1      *      8    =    8
19      1     *     1      *      7    =    7
20      1     *     1      *      6    =    6
                                         -------
                                           35

and the SUM bit just adds up all the end values of the products



If you exclude the last part of the formula, so that it becomes:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A"))

then what you end up with is a sum of a bunch of TRUE/FALSE values depending on whether or not the criteria has been met on that row, and this is the same as counting the number of records that meet your criteria. Imagine the above tables without Column C, and the last one would look like the following:-

        A           B             
 9      1     *     0    =    0
10      0     *     0    =    0
11      1     *     1    =    1
12      1     *     1    =    1
13      1     *     0    =    0
14      1     *     1    =    1
15      1     *     1    =    1
16      0     *     1    =    0
17      1     *     1    =    1
18      1     *     1    =    1
19      1     *     1    =    1
20      1     *     1    =    1
                           --------
                              8

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------