Urgent help please! sum ifs please help. Sum one column value if Column A is greater than Column B

[donggri84]

Hello,

I can't seem to create a correct formula for in cases where summing column D "sales target" of only "Division A" (Column A) if Value in column C is greater than value in Column B

This didn't work
=SUMIFS(D3:D60,A3:A160,"A",C3:C160,">B3:B160,B3")

Please help!

Division <tbody> </tbody>	Planned Launch Date <tbody> </tbody>	Actual Launch Date <tbody> </tbody>	Sales Target <tbody> </tbody>
A	1	5	$1
A	1	1	$6000
c	A	A	$0
C	A	A	$0
A	7	0	$25000
A	3	5	$4000
B	2	1	$5000
A	2	2	$4000
A	2	3	$3000
C	4	5	$5000
B	3	4	$2000
A	1	2	$3000
A	2	1	$2000
C	3	4	$1000

<tbody>
</tbody>

 

[AhoyNC]

Try:

=SUMPRODUCT(--(A3:A160="A"),--(C3:C160>B3:B160),D3:D160)

 

[donggri84]

Hello!

Thank you! But it isn't working and is giving me "0"
what is "--" if you be kind to inform?

Thank you!

 

[antialias401]

I tested AhoyNC's formula and it displays the proper result.

As a test, on a test page, change the values to $1 so it can be a bit more readable.
(The "--" changes a "True" into a "1" and a "False" into a 0, so it can used as a number)

Note: the formula starts calculating on A3, because your OP Formula started at A3
You can change it to A2 if need be.

AhoyNC's Formula tested & works (Excel 2010), starting with cell A2

=SUMPRODUCT(--(A2:A160="A"),--(C2:C160>B2:B160),D2:D160)

Hope this helps

 

[donggri84]

I tested AhoyNC's formula and it displays the proper result.

As a test, on a test page, change the values to $1 so it can be a bit more readable.
(The "--" changes a "True" into a "1" and a "False" into a 0, so it can used as a number)

Note: the formula starts calculating on A3, because your OP Formula started at A3
You can change it to A2 if need be.

AhoyNC's Formula tested & works (Excel 2010), starting with cell A2

=SUMPRODUCT(--(A2:A160="A"),--(C2:C160>B2:B160),D2:D160)

Hope this helps

Thank you!
with "--" seems to work.
but just for my learning experience, could you kindly inform me what "--" is?

Thank you!
Sorry! I'm very new!

 

[donggri84]

Thank you!
with "--" seems to work.
but just for my learning experience, could you kindly inform me what "--" is?

Thank you!
Sorry! I'm very new!

Actually, aahhhh its not working properly. It isn't summing up all

 

[donggri84]

I just saw that "--" thank you for the explanation!

 

[AhoyNC]

donrrri84
If you haven't been able to get it to work, please post a small sample of where it fails. Also, advise what answer you expect to see.

 

[craigexcel]

I think you may need to confirm the formula with [Ctrl][Shift][Enter]<ctrl><shift><enter>, and not just [Enter]<enter>. That hasn't been mentioned in the thread to date, and is often a stumbling block.</enter></enter></shift></ctrl>