Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum If
A B C D E Div GLEntry GL# Div Div Total 1 $(7,954.07) 1 -$1934.64 1 $(1,342.00) 1 02 $4400.00 1 $ (592.54) 1 03 -$1198.35 1 $(4,629.00) 04 $-.10 02 $5000.00 1 02 $ (600.00) 1 02 $ 77.77 03 $ 19.00 03 $ 694.05 1 03 $ 100.91 03 $ (13.00) 03 $(1,892.40) 1 04 $(2,588.56) 04 $ (0.10) 1 What I would like to do: If Col A =1 and Col C=1 then I would like for it to sum the amounts from Col B in the Corresponding Div in Col E (as per my totals in Col E). Thank you very much for any help you can give me. Donna |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum If
I apologize that my question did not hold the Column Format, hopefully this
one will. If not, can someone tell me how to fix it "Donna" wrote: A B C D E Div GLEntry GL# Div Div Total 1 $(7,954.07) 1 -$1934.64 1 $(1,342.00) 1 02 $4400.00 1 $ (592.54) 1 03 -$1198.35 1 $(4,629.00) 04 $-.10 02 $5000.00 1 02 $(600.00) 1 02 $77.77 03 $19.00 03 $694.05 1 03 $100.91 03 ($13.00) 03 $(1,892.40) 1 04 $(2,588.56) 04 $(0.10) 1 What I would like to do: If Col A =1 and Col C=1 then I would like for it to sum the amounts from Col B in the Corresponding Div in Col E (as per my totals in Col E). Thank you very much for any help you can give me. Donna |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum If
In G2: =SUMPRODUCT((A$2:A$15=D2)*(C$2:C$15=1),B$2:B$15)
Copy down. Adapt the ranges to suit. voila? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Donna" wrote: I apologize that my question did not hold the Column Format, hopefully this one will. If not, can someone tell me how to fix it "Donna" wrote: A B C D E Div GLEntry GL# Div Div Total 1 $(7,954.07) 1 -$1934.64 1 $(1,342.00) 1 02 $4400.00 1 $ (592.54) 1 03 -$1198.35 1 $(4,629.00) 04 $-.10 02 $5000.00 1 02 $(600.00) 1 02 $77.77 03 $19.00 03 $694.05 1 03 $100.91 03 ($13.00) 03 $(1,892.40) 1 04 $(2,588.56) 04 $(0.10) 1 What I would like to do: If Col A =1 and Col C=1 then I would like for it to sum the amounts from Col B in the Corresponding Div in Col E (as per my totals in Col E). Thank you very much for any help you can give me. Donna |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum If
I copied the formula in but I get all zero's. What am I missing.
Thanks Donna "Max" wrote: In G2: =SUMPRODUCT((A$2:A$15=D2)*(C$2:C$15=1),B$2:B$15) Copy down. Adapt the ranges to suit. voila? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Donna" wrote: I apologize that my question did not hold the Column Format, hopefully this one will. If not, can someone tell me how to fix it "Donna" wrote: A B C D E Div GLEntry GL# Div Div Total 1 $(7,954.07) 1 -$1934.64 1 $(1,342.00) 1 02 $4400.00 1 $ (592.54) 1 03 -$1198.35 1 $(4,629.00) 04 $-.10 02 $5000.00 1 02 $(600.00) 1 02 $77.77 03 $19.00 03 $694.05 1 03 $100.91 03 ($13.00) 03 $(1,892.40) 1 04 $(2,588.56) 04 $(0.10) 1 What I would like to do: If Col A =1 and Col C=1 then I would like for it to sum the amounts from Col B in the Corresponding Div in Col E (as per my totals in Col E). Thank you very much for any help you can give me. Donna |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum If
I don't know, but here's a little sample file which demos it in full working
order, based on your data as originally posted: http://cjoint.com/?ktqaMadaWT The sumproduct extracts exactly the results that you seek in your col E Any good? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Donna" wrote: I copied the formula in but I get all zero's. What am I missing. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum If
"Donna" wrote:
I copied the formula in but I get all zero's. What am I missing The earlier sample file shows that the "core" expression should work If its still returning zeros/incorrect totals for you, that means you have data inconsistency somewhere, eg: text numbers, or mixed text numbers/real numbers Try these increasingly heavier duty versions in G2, copied down: =SUMPRODUCT((A$2:A$15=D2)*(C$2:C$15=1),B$2:B$15+0) The: +0 bit will coerce col B's text nums (if any) to real nums for correct summing, w/o impacting the source numerical values =SUMPRODUCT((A$2:A$15&""=D2&"")*(C$2:C$15&""="1"), B$2:B$15+0) The: &"" bit will make it all to text nums in cols A, C , D, the: ="1" likewise as a text num, to enable consistent matching/comparisons in the criteria portion of the expression Success? Celebrate it, hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|