SUMIF with alphanumeric criteria range question

S

Steve

Dear group members,
I am trying to sum up entries from different categories represented by alphanumeric string. I need sums from multiple categories that are noncontinuous, such as a sum for Education (H5000-H5200,X3500). How can I set the criteria as sth like >H5000 AND <H5200 OR =X3500? Or how can I extract the numbers (I know how to separate letters from strings) and do SUMIFS with multiple criteria? Or how can I build a function for this purpose?


In the sample below, the left column contains the categories and right column their values.
contributor_category PAC_sum
274432
A1200 12500
B2400 3000
B4000 1500
C1100 2000
C2100 1000
C2200 6500
C4100 11000
C5120 1000
C5130 3000
D2000 10000
D3000 1000
D9000 1000
E1600 12500
E1610 2000

Thanks in advance!
 
C

Claus Busch

Hi Steve,

Am Sat, 15 Sep 2012 20:28:27 -0700 (PDT) schrieb Steve:
In the sample below, the left column contains the categories and right column their values.
contributor_category PAC_sum
274432
A1200 12500
B2400 3000
B4000 1500
C1100 2000
C2100 1000
C2200 6500
C4100 11000
C5120 1000
C5130 3000
D2000 10000
D3000 1000
D9000 1000
E1600 12500
E1610 2000
Click to expand...

for >H5000 and <H5200 you can try:
=SUMPRODUCT(--(LEFT(A1:A15,1)="H"),--(RIGHT(A1:A15,4)>"5000"),--(RIGHT(A1:A15,4)<"5200"),B1:B15)
for X3500 you can add
=SUMIF(A1:A15,"X3500",B1:B15)


Regards
Claus Busch
 
J

joeu2004

Steve said:
I need sums from multiple categories that are noncontinuous,
such as a sum for Education (H5000-H5200,X3500). How can I
set the criteria as sth like >H5000 AND <H5200 OR =X3500?
Or how can I extract the numbers (I know how to separate
letters from strings) and do SUMIFS with multiple criteria?
Or how can I build a function for this purpose?
Click to expand...

There is no need to separate the letter and the numbers, if we can assume
the numbers are always 4 digits.

If you can use SUMIFS (Excel 2007 or later), then do the following:

=SUMIFS(B1:B100,A1:A100,">=H5000",A1:A100,"<=H5200")
+ SUMIFS(B1:B100,A1:A100,"X3500")

If you require Excel 2003 compatibility, then do either of the following:

=SUMIF(A1:A100,">=H5000",B1:B100) - SUMIF(A1:A100,">H5200",B1:B100)
+ SUMIF(A1:A100,"X3500",B1:B100)

or

=SUMPRODUCT((A1:A100>="H5000")*(A1:A100<="H5200"),B1:B100)
+ SUMIF(A1:A100,"X3500",B1:B100)

In the last example, multiplication (*) acts like AND, which we cannot use
in this context with the intended results. It also has the effect of
converting TRUE and FALSE to 1 and 0, which SUMPRODUCT requires.

If we cannot assume the numbers are always 4 digits, the following can be
used:

=SUMPRODUCT((LEFT(A1:A100,1)="H")
*(--MID(A1:A100,2,99)>=123)*(--MID(A1:A100,2,99)<4567),B1:B100)
+ SUMIF(A1:A100,"X3500",B1:B100)

For this example, I am assuming we want to sum the PAC amounts for
categories H123 through H4567 and X3500. The double negative (--) converts
the text result of MID to a number.
 
J

joeu2004

Errata.... I said:
=SUMPRODUCT((LEFT(A1:A100,1)="H")
*(--MID(A1:A100,2,99)>=123)*(--MID(A1:A100,2,99)<4567),B1:B100)
+ SUMIF(A1:A100,"X3500",B1:B100)

For this example, I am assuming we want to sum the PAC amounts
for categories H123 through H4567 and X3500.
Click to expand...

The <4567 should be <=4567.
 
Top