Sumproduct

[rmm30]

Hello -

Can anyone help me? WARNING: I am a novice excel user.

My formula is totaling in one cell. I would like it to total Januar
in one cell, February total in another, etc.

Column A has the vendor name
Column D has the date (as 1,2,3...for Jan, Feb, Mar)
Column F has the values

The formula below is totaling the amounts in one cell.

=SUMPRODUCT(('Owners Act'!A3:A1000="David L. Schmitt (Consulting
Law")*(MONTH('Owners Act'!D31000)=1)
*('Owners Act'!F3:F1000))

I assume that the above formula would total only January (=1) instea
it is totaling all values, albeit with the correct vendor name.

I hope that is clear enough

 

[Peo Sjoblom]

Remove the MONTH function, it will return January for everything if you use
the index number
of the months, just use the range =1. The month function works only with
real dates and since
1 - 12 will be 01/01/1900 - 01/12/1900 (excel starts with Jan 0 1900) just
use range=1

 

[rmm30]

Thanks for the reply....

I removed the MONTH as you recommended, but now the nothing shows i
the cell. Here is the revised formula. What am I doing wrong? Ca
show me an example of what you mean?

=SUMPRODUCT(('Owners Act'!A3:A1000="David L. Schmitt (Consulting
Law")*('Owners Act'!D31000)=1)
*('Owners Act'!F3:F1000)

Thanks very much

 

[rmm30]

Hello -

Can anyone help me? WARNING: I am a novice excel user.

My formula is totaling in one cell. I would like it to total January i
one cell, February total in another, etc.

Column A has the vendor name
Column D has the date (as 1,2,3...for Jan, Feb, Mar)
Column F has the values

The formula below is totaling the amounts in one cell.

=SUMPRODUCT(('Owners Act'!A3:A1000="ABC Company")*(MONTH('Owner
Act'!D31000)=1)
*('Owners Act'!F3:F1000))

I assume that the above formula would total only January (=1) instea
it is totaling all values, albeit with the correct vendor name.

I hope that is clear enough.

****I removed the "MONTH" from the formula as Peo Sjoblom suggested bu
it still is not working. With the edit, the cell is now now empty.

Sample file attached! Thanks!!

Attachment filename: frankk.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=53236

 

[Domenic]

Try,

For specific company and month:

=SUMPRODUCT((A1:A1000="ABC Company")*(D11000=1)*F1:F1000)

For specific month only:

=SUMPRODUCT((D11000=1)*F1:F1000)

Hope this helps!

 

[rmm30]

OK - I got it Peo Sjoblom!

This was my original formula:
=SUMPRODUCT(('Owners Act'!A3:A1001="ABC Company")*(MONTH('Owner
Act'!D31001)=1)
*('Owners Act'!F3:F1001))

It was totalling everything in one cell.

Peo Sjoblom told me to remove the "MONTH" from the formula. I did tha
but the formula still was not working.

Then it dawned on me what he meant. See below:

=SUMPRODUCT(('Owners Act'!A3:A1001="ABC Company)*('Owner
Act'!D31001=1)*('Owners Act'!F3:F1001))

I'm so stupid. I forgot to remove the " ) " from before the "=1".

It works now. Thanks Peo Sjoblom

 

[rmm30]

Yup, it helped Domenic. I double checked my work against you
suggestion. I'm good! Things are working perfectly