Hello and help! Either Sumproduct or Countif?

Retrojay

New Member
Joined
Oct 13, 2013
Messages
20
Hi everyone,

I am a bit stuck.

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Sales Br[/TD]
[TD]Del date[/TD]
[TD]New Used[/TD]
[TD]Postcode[/TD]
[TD]Sale Type[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]01/05/2014[/TD]
[TD]U[/TD]
[TD]XX16 5XX[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]01/05/2014[/TD]
[TD]U[/TD]
[TD]XX16 5XX[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]02/05/2014[/TD]
[TD]N[/TD]
[TD]XX16 5XX[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]02/05/2014[/TD]
[TD]N[/TD]
[TD]XX16 5XX[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]03/05/2014[/TD]
[TD]U[/TD]
[TD]XX16 5XX[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]04/05/2014[/TD]
[TD]N[/TD]
[TD]XX16 5XX[/TD]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]











I want to count up the number of times a product is bought at Sales Br 1 and is "N" and has Sale Type "R" IF the Del Date ='Input Sheet'!C3

So to Psudeo code it

If Del Date = 'Input Sheet'!C3 AND Sales Br = 1 AND Sales Type ="R" THEN count the number of times this occurs.

Does this make sense? It is just not clicking with me and I have tried :confused::confused:

Any help you can give me would be appreciated guys :)

Thanks in advance

Jay
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi Jay.

Assuming your table starts in A1, try this:

=COUNTIFS(A2:A7,1,B2:B7,'Input Sheet'!C3,E2:E7,"R")
 
Upvote 0
Thanks for the quick response bbot :)

My current formula is =COUNTIFS('Sales Data'!D2:D1198,C3,'Sales Data'!C2:C940,1,'Sales Data'!F2:F917,"N",'Sales Data'!I2:I741,"R")

This is trying to count up a range where the date is D2:D1198 in sales data and match this to the date in C3
AND if the Range F2:F....

Actually I have just realised why I am getting #VALUE back whilst typing this. The ranges aren't the same!!!!!

See sometimes it just takes talking to someone about it!

Thanks anyway dude :)
 
Upvote 0
...actually I have an add on!

=COUNTIFS('Sales Data'!D2:D1198,C3,'Sales Data'!C2:C940,1,'Sales Data'!F2:F917,"N",'Sales Data'!I2:I741,"R")

What about if I wanted to say in the last array, is EITHER "R" OR "N" OR "E"

I have tried OR but it doesn't seem to work
 
Upvote 0
Why are your SUMIF ranges different lengths? Surely they should all be the same size?

Try (untested)

=SUMPRODUCT(('Sales Data'!D2:D1741=C3)*('Sales Data'!C2:C1741=1)*('Sales Data'!F2:F1741="N")*(('Sales Data'!I2:I741="R")+('Sales Data'!I2:I741="N")+('Sales Data'!I2:I741="E")))

NOTE: SUMPRODUCT requires all the ranges to be the same length.
 
Last edited:
Upvote 0
Yeah I noticed that as I was typing, I thought I had a grasp on the Formula but it was the detail that I was lacking...

I have gone with this and normalised all the ranges.

=COUNTIFS('Sales Data'!$D$2:$D$2000,D3,'Sales Data'!$C$2:$C$2000,1,'Sales Data'!$F$2:$F$2000,"N",'Sales Data'!$I$2:$I$2000,"R")+COUNTIFS('Sales Data'!$D$2:$D$2000,D3,'Sales Data'!$C$2:$C$2000,1,'Sales Data'!$F$2:$F$2000,"N",'Sales Data'!$I$2:$I$2000,"E")+COUNTIFS('Sales Data'!$D$2:$D$2000,D3,'Sales Data'!$C$2:$C$2000,1,'Sales Data'!$F$2:$F$2000,"N",'Sales Data'!$I$2:$I$2000,"D")

Which one of these are better processing wise?
 
Upvote 0
This one is returning #N/A

=SUMPRODUCT(('Sales Data'!D2:D1741=C3)*('Sales Data'!C2:C1741=1)*('Sales Data'!F2:F1741="N")*(('Sales Data'!I2:I741="R")+('Sales Data'!I2:I741="N")+('Sales Data'!I2:I741="E")))

...and mine isn't adding anything up

=COUNTIFS('Sales Data'!$D$2:$D$2000,C3,'Sales Data'!$C$2:$C$2000,1,'Sales Data'!$F$2:$F$2000,"N",'Sales Data'!$I$2:$I$2000,"R")+
COUNTIFS('Sales Data'!$D$2:$D$2000,C3,'Sales Data'!$C$2:$C$2000,1,'Sales Data'!$F$2:$F$2000,"N",'Sales Data'!$I$2:$I$2000,"E")+
COUNTIFS('Sales Data'!$D$2:$D$2000,C3,'Sales Data'!$C$2:$C$2000,1,'Sales Data'!$F$2:$F$2000,"N",'Sales Data'!$I$2:$I$2000,"D")



 
Upvote 0
Ah!! It is working actually. Here's the thing though, i am running Excel 2010 and because I am comparing DATES in the formula it is auto formatting the field to a date every time I run it!

Can I stop it doing this?
 
Upvote 0

Forum statistics

Threads
1,224,522
Messages
6,179,297
Members
452,903
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top