# SUMIF with AND for range and criteria

1. ## SUMIF with AND for range and criteria

Hi,

I a bit of an Excel newbie, and can't figure this one out. Maybe it's easy for someone out there?

I need to be able to do a SUMIF based on 2 seperate columns in order to put a sum of values from one sheet into a summary on another.

For example,

Sheet1
A B C
John Smith 40
Joe Bloggs 38
John Smith 24
Jane Smith 36

Sheet2
X Y Z
John Smith 64

So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C, based on BOTH A matching X and B matching Y.

Dave.

2. It's not really clever, but I would run two helper columns, a new column C on sheet1 =A1&" "&B1 and a new column Z on sheet2 =X1&" "&Y1
this would give me a safety check on the match and an easy SumIf.

and the total column, now in AA, then reads

=SUMIF(Sheet1!C1:Sheet1!C4,Z1:Z4,Sheet1!D1:D4)

I presume that you know to formula-drag down the column for the extent of your data.

Originally Posted by davidm_ba
Hi,

I a bit of an Excel newbie, and can't figure this one out. Maybe it's easy for someone out there?

I need to be able to do a SUMIF based on 2 seperate columns in order to put a sum of values from one sheet into a summary on another.

For example,

Sheet1
A B C
John Smith 40
Joe Bloggs 38
John Smith 24
Jane Smith 36

Sheet2
X Y Z
John Smith 64

So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C, based on BOTH A matching X and B matching Y.

Dave.

3. Thanks Bryan,

It doesn't have to be clever, as long as it works, and that seems like a straight-forward way to do it.

Dave.

4. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

5. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

6. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

7. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

8. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

9. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

10. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

11. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

12. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

13. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

14. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

15. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

16. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

17. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

18. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

19. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

20. ## Re: SUMIF with AND for range and criteria

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

--
HTH

Bob Phillips

"davidm_ba" <davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com> wrote
in message news:davidm_ba.1t4ymk_1122984373.5108@excelforum-nospam.com...
>
> Hi,
>
> I a bit of an Excel newbie, and can't figure this one out. Maybe it's
> easy for someone out there?
>
> I need to be able to do a SUMIF based on 2 seperate columns in order to
> put a sum of values from one sheet into a summary on another.
>
> For example,
>
> Sheet1
> A B C
> John Smith 40
> Joe Bloggs 38
> John Smith 24
> Jane Smith 36
>
> Sheet2
> X Y Z
> John Smith 64
>
>
> So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
> based on BOTH A matching X and B matching Y.
>
>
>
> Dave.
>
>
> --
> davidm_ba
> ------------------------------------------------------------------------
> davidm_ba's Profile:

http://www.excelforum.com/member.php...o&userid=25811
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1