Basic SUMIF formula not working

Question

Hi all,

Here is a small sample of my data.

I have some data showing an order number, date and occurances of an event per crew.

I am trying to set up another table that tallys up all events per crew on a specific date. Unfortunately, my SUMIF formula is not working. I tried 2 variations both of which can be seen under the A or B collumn.

Main Data:

A                       B                          C                  D               E               F                 G

Date	Order	Occurances	Yards produced	Yards Scrapped	A occurances	B occurances
01-Mar-13	107900218	3	2000	110		3
01-Mar-13	107900219	1	4495	0		1
01-Mar-13	107900220	4	7900	30		1.32
01-Mar-13	107900221	0	3080	25
01-Mar-13	107900222	1	6080	100
01-Mar-13	107900223	1	8400	20
02-Mar-13	107900224	1	6180	55
02-Mar-13	107900225	2	5250	90
02-Mar-13	107900226	2	3480	5
02-Mar-13	107900227	3	29845	80	0.9
02-Mar-13	107900228	3	8615	120	3
02-Mar-13	107900229	4	26395	265	0.4
03-Mar-13	107900230	0	6300	110
03-Mar-13	107900231	0	3300	100
03-Mar-13	107900232	1	3685	105
03-Mar-13	107900233	14	20600	300		14
03-Mar-13	107900234	2	7600	30		2
03-Mar-13	107900235	3	3380	40		3
03-Mar-13	107900236	2	3775	15		2

Second table:

P                      Q                R

Date	A	B
01-Mar-13	0	0
02-Mar-13	0	0
03-Mar-13	0	0

The Formula under A is:

=SUMIF(A:A,A:A =P2,F:F )

Under B:

=SUMIF(A:A, P2,G:G )

If someone can help me get this working, I'd really appreciate it.

Thanks a lot!

Answer 1

Hi,

A Pivot Table should work fine here.  Drag dates to the row labels and columns which you want to summarise to the Value area.

Hope this helps.

Answer 2

Hi, try

=sumproduct(--(P2=A:A),F:F)

Answer 3

ZamTO wrote:

[ediardp wrote:]

=sumproduct(--(P2=A:A),F:F)

Thanks for the reply.   This only gives me the first number of the date though

It is a "bad practice" to use whole-range references like A:A with SUMPRODUCT, even though Excel 2007 and later permit it, unfortunately.  The example above results in at least 3+ million operations as well as 1+ million (probably 8+ million) bytes of memory for one (or two) temporary arrays.

It would be better to write:

=SUMPRODUCT(--(A1:A10000=P2),F1:F10000)

Adjust 10000 up or down as you please.  But I doubt that you ever have 1+ million rows of values.

As for the second calculation, it is unclear to me why SUMIF(A:A,P2,G:G) does not work, if the SUMPRODUCT for the first calculation works.

But the equivalent SUMPRODUCT formula would be:

=SUMPRODUCT(--(A1:A10000=P2),G1:G10000)

And note Bernard's response:  =SUMIF(A:A,P2,F:F) should work.

However, it has been pointed out elsewhere (click here) [1] and I confirmed that SUMIF and SUMIFS is unpredictably unreliable.  The operative word is "unpredictably".  It makes me very uncomfortable to use SUMIF and SUMIFS now.

---

[1] http://social.technet.microsoft.com/Forums/en/excel/thread/dc952764-7f54-4545-a87d-2d3b0095000c

Answer 4

Hi, try

=sumproduct(--(P2=A:A),F:F)

Thanks for the reply

This only gives me the first number of the date though

Answer 5

You need =SUMIF(A:A,P2,F:F)

best wishes