Sumproduct with dates conditional on value in another column 4

[BxWill]

Not having much success with the use of sumproduct to calculate the sum of records by name by month, as displayed in the following scenario.

Name----Month-------Amount---SumByNameByMonth
Jill----1/1//2014---$100-----$250
Jill----1/1/2014----$150-----$250
Jill----2/1/2014----$200-----$200
Bill----1/1/2014----$250
Bill----2/1/2014----$300
Bill----2/1/2014----$400
Bob-----1/1/2014----$150
Bob-----6/1/2014----$325

Ideally, I would like to enter a sumproduct formula in the second row of the column titled "SumByNameByMonth" and just copy down the column.

(Of course I could perform a pivot. But I am interested in populating a 30,000 record spreadsheet relatively quickly...)

Any insight as to how the sumproduct formula should be constructed?

Thanks in advance.

 

[Deniall]

If your data table is in range B313 place the formula
=SUMPRODUCT(--($B$3:$B$13=B3)*($C$3:$C$13=C3),$D$3:$D$13)
in cell E3, then copy down.

But it requires that you use fix up your MONTH column first.

 

[BxWill]

Thanks! This worked.

I did not have success using the asterisks but it appears that the use of the double hyphens did it.

 

[Deniall]

Just for the record, what you are calling double hyphens is actually interpreted by Excel as a pair of unary minus symbols.[ ] They are put there to ensure that Excel treats all the stuff that follows as numeric, because the "trick" being performed here is to utilise the arithmetic values of the results of the logical operations ($B$3:$B$13=B3) and ($C$3:$C$13=C3).[ ] You need two of them so that they do not end up changing the sign of the end result.

There are other "tricks" you could use instead of "--":[ ] for example "1*".

In this case, using Excel 10 anyway, the formula works OK even without the "--".[ ] I do not fully understand why the trick is sometimes needed and sometimes not needed, so I tend always to use it.

 

[Andrzejek]

FYI, you can show your data a lot better with [tt][ignore][pre] ... [/pre][/ignore][/tt] tags so it will show like this:

[pre]
Name Month Amount SumByNameByMonth
Jill 1/1//2014 $100 $250
Jill 1/1/2014 $150 $250
Jill 2/1/2014 $200 $200
Bill 1/1/2014 $250
Bill 2/1/2014 $300
Bill 2/1/2014 $400
Bob 1/1/2014 $150
Bob 6/1/2014 $325
[/pre]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[BxWill]

did change a formula in a column and the sumproduct formula is no longer working.

For example,

I changed the formula in a column (column Q)

from "=IF(ISERROR(08-N8)," ",(O8-N8))

to "=IF(ISERROR(N8-ON8)," ",(N8-O8))"

Note, the sumproduct formula that I am using is;

=SUMPRODUCT(--($A$8:$A$3000=A8)*($D$8:$D$3000=D8),$Q$8:$Q$3000)

Did re-enter the sumproduct formula to no avail.



Any insight as to a possible resolution is appreciated.

 

[BxWill]

Note, the date column - column D - is formatted as date in the format M/D/YYYY.

To verify that I do indeed have dates in the column, I changed the format to "General" and the serial numbers are displayed.

Still attempting to resolve...

 

[BxWill]

I resolved. Had to re-enter the formula...

 

[SkipVought]

=SUMPRODUCT(--($A$8:$A$3000=A8)*($D$8:$D$3000=D8)*($Q$8:$Q$3000))

No COMMA!