Excel SUMIF formula statisfying two conditions in two columns?

G

Guest

The SUMIF formula in the active worksheet must total the the values in a
column for rows in another worksheet that satisfy a different condition in
each of two columns (cell ranges) in that same work sheet. The conditions
are also referenced in the active worksheet
Example: For all rows in other worksheet where column A = X and column B =
Y, sum column C.
What I have tried so far without success is
=SUMIF(AND(Other!A5:A400,Other!N5:N400),AND(A14,C15),Other!C5:C400)
 
J

JulieD

Hi

one option

=SUMPRODUCT(--(Other!A5:A400=A14),--(Other!N5:N400=C15),Other!C5:C400)

Cheers
JulieD
 
B

Bernie Deitrick

Joe,

Try

=SUMPRODUCT((Other!A5:A400="X")*(Other!B5:B400="Y")*Other!C5:C400)

HTH,
Bernie
MS Excel MVP
 
G

Guest

Hi! Thanks for the response. Tried your suggestion . . .

=SUMPRODUCT(--(Cards!$A$5:$A$479=G57),--(Cards!$N$5:$N$479=A57),Cards!$E$5:$E$479) but the formula returned a zero when there is a value >2

Some more detail. "Cards" is the sheet containing the major data file where
column A is the card number from 0002 to 8500 in general format. Column N is
the work package the card is scheduled in , also general format, with an
additional row for that card number if it is in more than one package.
Column E contains the labor standard for that card in that package, in hours
formatted as a number. On the active sheet "Packages", the package
designation is in Column A in general format. The row associated with each
package contains the cards in that package, starting at G. I am trying to
pull the labor hours from the "Cards" sheet's column E into the active sheet
"Packages" for each work package's card number in it's row. The formula must
satisfy a true for both the card number and package number to pull the
"Sheets" column E labor hours data into the corresponding cells in the
specific package row. I have also tried . . . .

=SUMPRODUCT(--(ISNUMBER(SEARCH(G57,Cards!$A$5:$A$479))),--(Cards!$N$5:$N$479=A57),--(Cards!$E$5:$E$479)) Which produced a #value error.

Appreciate your help. What do you think?
JoeR
 
G

Guest

Hi!
Tried your suggestion and some others as well. Getting 0s or #value errors.
I responded to another suggestion before yours. Here's what I got . . .


=SUMPRODUCT(--(Cards!$A$5:$A$479=G57),--(Cards!$N$5:$N$479=A57),Cards!$E$5:$E$479) but the formula returned a zero when there is a value >2

Some more detail. "Cards" is the sheet containing the major data file where
column A is the card number from 0002 to 8500 in general format. Column N is
the work package the card is scheduled in , also general format, with an
additional row for that card number if it is in more than one package.
Column E contains the labor standard for that card in that package, in hours
formatted as a number. On the active sheet "Packages", the package
designation is in Column A in general format. The row associated with each
package contains the cards in that package, starting at G. I am trying to
pull the labor hours from the "Cards" sheet's column E into the active sheet
"Packages" for each work package's card number in it's row. The formula must
satisfy a true for both the card number and package number to pull the
"Sheets" column E labor hours data into the corresponding cells in the
specific package row. I have also tried . . . .

=SUMPRODUCT(--(ISNUMBER(SEARCH(G57,Cards!$A$5:$A$479))),--(Cards!$N$5:$N$479=A57),--(Cards!$E$5:$E$479)) Which produced a #value error.

Appreciate your help. What do you think?
JoeR
 
B

Bernie Deitrick

Joe,

From the sounds of it, your numbers are actually strings: the general format
should show 2, not 0002. So try entering the number as a string: for
example use

'0002

into the cell that you are using for your key when you want to find the
0002.

HTH,
Bernie


Joe R. said:
Hi!
Tried your suggestion and some others as well. Getting 0s or #value errors.
I responded to another suggestion before yours. Here's what I got . . .
Click to expand...
=SUMPRODUCT(--(Cards!$A$5:$A$479=G57),--(Cards!$N$5:$N$479=A57),Cards!$E$5:$
E$479) but the formula returned a zero when there is a value >2
Some more detail. "Cards" is the sheet containing the major data file where
column A is the card number from 0002 to 8500 in general format. Column N is
the work package the card is scheduled in , also general format, with an
additional row for that card number if it is in more than one package.
Column E contains the labor standard for that card in that package, in hours
formatted as a number. On the active sheet "Packages", the package
designation is in Column A in general format. The row associated with each
package contains the cards in that package, starting at G. I am trying to
pull the labor hours from the "Cards" sheet's column E into the active sheet
"Packages" for each work package's card number in it's row. The formula must
satisfy a true for both the card number and package number to pull the
"Sheets" column E labor hours data into the corresponding cells in the
specific package row. I have also tried . . . .
Click to expand...
=SUMPRODUCT(--(ISNUMBER(SEARCH(G57,Cards!$A$5:$A$479))),--(Cards!$N$5:$N$479
=A57),--(Cards!$E$5:$E$479)) Which produced a #value error.
 
G

Guest

Bernie,
I appreciate your help. I'm still having trouble. Using the formula . . .
=SUMPRODUCT(--(Cards!$A$5:$A$479=G57),--(Cards!$N$5:$N$479=$A57),Cards!$E$5:$E$479)
provides the correct data for one cell, but not the next where it returns a
zero when there is a value. The formula works in some cells, but not for the
majority even though the formatting of the data is consistent. Any ideas?
 
J

JulieD

Hi Joe

can you type out a sample (say 5 lines of your data - just for these
columns) with the results that you see (one right one & four wrong ones) -
it might help us get a better handle on the problem

Cheers
JulieD
 
B

Bernie Deitrick

Joe,

The general way to troubleshoot these things is to delete all the rows from
your data table except those for one value only, and choose a value that you
know isn't being properly counted. So let's say that of the 475 rows, there
are only 5 for '0002, which isn't working properly. When you delete all but
those five - you can sort prior to deletion to speed up the selection of
rows to delete - your formula will end up being

=SUMPRODUCT(--(Cards!$A$5:$A$9=G57),--(Cards!$N$5:$N$9=$A57),Cards!$E$5:$E$9
)

Highlight the sub formulas by parts: first
Cards!$A$5:$A$9=G57
and then press F9.

This will convert that section to an array of TRUE and FALSE values,
corresponding to the individual cells. Then you can see which cell values
are retruning FALSE when you think they should return TRUE, and you can
figure things out from there.

HTH,
Bernie
MS Excel MVP
 
G

Guest

Hi Julie,

Sheet labeled "Cards!" Column A is a series of card numbers, Column N is a
list of work packages, and Column E is a labor standard number calculated
from a formula in that cel.

Example . . .

Column A Column E Column
N

1438 =IF($R30<>"",$C30*$R30,$C30) 1.41 BC01X
1439 =IF($R31<>"",$C31*$R31,$C31) 0.35 BC02A
1440 =IF($R32<>"",$C32*$R32,$C32) 0.89 BC02B
1441 =IF($R33<>"",$C33*$R33,$C33) 4.06 BC03A
1446 =IF($R34<>"",$C34*$R34,$C34) 1.29 BC03B

Columns A & E are in number format, N is general.

On Sheet "Pkg Schedule"

Column A is list of work packages ("Cards" Column N
Same row, Column G thru Z contain list of cards assigned to that package.
Next row columns G thru Z are to indicate labor standard from "Cards" column
E for the card number directly above and for the work package in column A.
(Note: Same card number has different standard for another work package.)
Column G produces the correct standard number while H thru K produce a zero.
Then L and O are OK, but the rest return zeroes as well. Same for other
rows, but not consistent to same columns.

Sheet labeled "Pkg Schedule" . . .

Column A Columns G H I J K
L M N

BC01X 1438 1440 1441 1446 1466 1703
1704 1707
1.41 0.00 0.00 0.00 0.00
2.00 4.45 0.00
BC02A 1438 1446 1466 1515 1575 1725
1780 1781
0.00 1.33 0.00 0.00 0.00
1.00 0.00 0.00
BC02B 1639 1640 1681 1690 1699 1703
1704 1707
1.71 0.00 0.00 0.00 2.10
6.45 0.00 0.00
BC03A 1438 1446 1466 1515 1575 1725
1780 1781
0.00 0.00 3.50 0.41 0.00
0.00 6.00 0.00

The numbers are correct when they pop in, but over 90% of the 0.00s should
show a value >.2. Has me buffalo'd! Hope this example helps. Thanks for
taking the time to help.

Joe R.
 
J

JulieD

Hi Joe

thanks for the data .. makes a bit more sense now but from my reading of the
example you want to say
in cell O1
IF(AND(A1=Cards!N1,G1=Cards!A1),Cards!E1, ... etc
ie if the value in N1 is BC01X and the value in A1 is 1438 put 1.41 in the
cell ... if not, go and look for another combination and put that in
i don't see any SUMing anywhere

have i interpreted it correctly?

if so, we'll see if we can come up with the easiest way to do this ..

Cheers
JulieD
 
G

Guest

To JulieD & Bernie,
Thanks for your help. Actually, your original formulas worked. Although I
thought I had formatted the cells correctly, for some reason they kept going
back to neutral, or no format selection. Don't know why, but once I
reformatted everything, Julie's original suggestion worked. Thanks for both
your inputs.
 
G

Guest

Bernie Deitrick said:
Joe,

The general way to troubleshoot these things is to delete all the rows from
your data table except those for one value only, and choose a value that you
know isn't being properly counted. So let's say that of the 475 rows, there
are only 5 for '0002, which isn't working properly. When you delete all but
those five - you can sort prior to deletion to speed up the selection of
rows to delete - your formula will end up being

=SUMPRODUCT(--(Cards!$A$5:$A$9=G57),--(Cards!$N$5:$N$9=$A57),Cards!$E$5:$E$9
)

Highlight the sub formulas by parts: first
Cards!$A$5:$A$9=G57
and then press F9.

This will convert that section to an array of TRUE and FALSE values,
corresponding to the individual cells. Then you can see which cell values
are retruning FALSE when you think they should return TRUE, and you can
figure things out from there.

HTH,
Bernie
MS Excel MVP
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel SUMIF formula statisfying two conditions 8
Can I have two conditions in a SUMIF? 5
SUMIF with 2 conditions 20
Sorting with sumif function 1
SUMIF with two conditions (or) 7
SUMIF with two conditions? 7
SUMIF with two or more variables 1
select multiple cell ranges in "sumif" formula? 1

Top