Summing if several conditions apply

B

bexi_reyes

Hello,
I am not a very sophisticated Exce user. I can do simple single
functions, but my skills are very limited to array formulas. Can you
help me? I got stuck.
Sheet1
Column A Column B Column C
Column D
CH #-812081T CH815 6180 125000
HK #-812081T CH815 - 75000
TH #-752075T AS755 - 11000
RP #-752075T AS755 213.72 89000
WW #-532053T WW535 4941.41 43,000

Sheet2
ColumnA Column B Column C
CH #-812081T CH815 125000
CH #-812081T CH05 6180
HK #-812081T CH815 75000
HK #-532053T 5785 4519.58
RP #-752075T AS755 89000
RP #-752075T 213.72
SN #-532053T WW535 30000
SN #-532053T 425.83
SN #-532053T WW535 13000
TH #-752075T AS755 11000

Column D - I used the sumif function. If I copy the formula down, it
duplicates the amount. I want to be able to copy the formulas down and
if it does not find a match, it should return a dash. I tried including
a match to the sumif function but I got an error in my formula. The
criteria is column A and column B of sheet1. I entered the return value
in this column.

Column C - I also used the sumif fuction. It should not include column
D and it should include "53" if it finds this text in column C. I
entered the return value in this column.

What is the logical order when writting array formulas? Can you explain
it? This will help me in the future?

Thank you
 
E

Eddie O

Your explanation is a little hard to follow. Where I could, I've put
comments below in ALL CAPS. If you can clarify what you're trying to do, I
will try to help further.


[email protected] said:
Hello,
I am not a very sophisticated Exce user. I can do simple single
functions, but my skills are very limited to array formulas. Can you
help me? I got stuck.
Sheet1
Column A Column B Column C
Column D
CH #-812081T CH815 6180 125000
HK #-812081T CH815 - 75000
TH #-752075T AS755 - 11000
RP #-752075T AS755 213.72 89000
WW #-532053T WW535 4941.41 43,000

Sheet2
ColumnA Column B Column C
CH #-812081T CH815 125000
CH #-812081T CH05 6180
HK #-812081T CH815 75000
HK #-532053T 5785 4519.58
RP #-752075T AS755 89000
RP #-752075T 213.72
SN #-532053T WW535 30000
SN #-532053T 425.83
SN #-532053T WW535 13000
TH #-752075T AS755 11000

Column D - I used the sumif function. If I copy the formula down, it
duplicates the amount. I want to be able to copy the formulas down and
if it does not find a match, it should return a dash.
Click to expand...
SUMIF RETURNS A ZERO IF IT DOESN'T FIND A MATCH. YOU CAN THEN FORMAT THAT
ZERO TO LOOK LIKE A DASH, USING CUSTOM NUMBER FORMATTING.
I tried including
a match to the sumif function but I got an error in my formula. The
criteria is column A and column B of sheet1. I entered the return value
in this column.
Click to expand...
SUMIF CAN ONLY MATCH AGAINST ONE COLUMN, NOT TWO. IN ORDER TO SUM BASED ON
CONDITIONS IN TWO COLUMNS, YOU EITHER NEED TO JOIN THE CONTENTS OF THE TWO
COLUMNS USING 'CONCATENATE' AND THEN HAVE YOUR SUMIF REFERENCE THE COLUMN
WITH THAT JOINED RESULT, OR YOU NEED TO USE AN ARRAY FORMULA. AN ARRAY
FORMULA WHICH SUMS AGAINST COLUMN A *AND* COLUMN B, FOR EXAMPLE, MIGHT LOOK
LIKE THIS: {=SUM(IF(A1:A1000="A",IF(B1:B1000="B",C1:C1000)))} THAT
FORMULA WOULD SUM THE CONTENTS OF COLUMN C WHERE COLUMN A CONTAINED "A" AND
COLUMN B CONTAINED "B". IN ORDER TO ENTER THE ARRAY FORMULA, YOU HAVE TO
HOLD DOWN CONTROL AND SHIFT AT THE SAME TIME, THEN HIT ENTER.
 
B

Bexi

Eddie,

The data is as follows:
Sheet1
A B C D
CH #-812081T CH815
HK #-812081T CH815
TH #-752075T AS755
RP #-752075T AS755
WWE#-532053T WW535

Sheet2
A B C D
CH #-812081T CH815 125,000 B
CH #-812081T CH05 6,180 A
HK #-812081T CH815 75,000 B
HK #-532053T 5785 4,519.58 A
RP #-752075T AS755 89,000 B
RP #-752075T 213.72 A
SN #-532053T WW535 30,000 B
SN #-532053T 425.83 A
SN #-532053T WW535 13,000 B
TH #-752075T AS755 11,000 B

I want the values from Sheet2 to show on Sheet1 on Columns C and D in
their respective row.
Sheet2 is export data from accounting where A=Actuals and B= Budget
shown on column D.

Column C is to show Actuals and it should sum the values found on
sheet2
Column D is to show Budget and it should sum the values found on sheet2

HK #-532053T and SN #-532053T should roll up to the WWE#-532053T for
both budget and actuals.

Thanks for your help.

Bexi
 
E

Eddie O

Bexi,
Now I understand. Sorry for the slow response here.
*Without* the complication of the WWE# rollup which you mentioned towards
the end, here's how your formulas would look.
For column C:
{=SUM(IF(A1=Sheet2!$A$1:$A$1000,IF(Sheet2!$D$1:$D$1000="A",Sheet2!$C$1:$C$1000)))}
Column D would be the exact same, except you'd substitute "B" for "A"

With the additional WWE# complication you'd need a more complicated formula:
{=IF(A1<>"WWE#-532053T",SUM(IF(A1=Sheet2!$A$1:$A$1000,IF(Sheet2!$D$1:$D$1000="A",Sheet2!$C$1:$C$1000))),SUM(IF("HK
#-532053T"=Sheet2!$A$1:$A$1000,IF(Sheet2!$D$1:$D$1000="A",Sheet2!$C$1:$C$1000)))+SUM(IF("SN
#-532053T"=Sheet2!$A$1:$A$1000,IF(Sheet2!$D$1:$D$1000="A",Sheet2!$C$1:$C$1000))))}
 
B

Bexi

Eddie,

I used the sumproduct in Column C withouth the WWE# complication. It
worked

Column C
=SUMPRODUCT(($A$12:$A$21=A3)*($B$12:$B$21="A"),$D$12:$D$21)

Column D
=SUMPRODUCT(($A$12:$A$21=A3)*($B$12:$B$21="B"),$D$12:$D$21)

The formula for the WWE# is n a separate section of my sheet and it
looks like this
=SUMPRODUCT(--(MID($A$12:$A$21,10,2)=E7)*(B12:B21="B"),$D$12:$D$21)

I am sure I will apply your formula in another Excel template design.

Thanks again and best regards,
Bexi
 
Top