I need a formula please

  • Thread starter Thread starter Vercingetorix.XIII
  • Start date Start date
V

Vercingetorix.XIII

I have 2 columns L and N that have payment dates in them, I need a formula
that will scan those 2 columsn because the payments are made in 2 equal
payments and add everything from another column into a cell made for that
date. adds everything together-sumif is working fine with only one column of
dates, but with 2 columns, some strange additions are happening. Any help is
appreciated.
Click to expand...


Client Debt Amount Commission 2 Pymnts of: 1st Payment 2nd Payment
client1 27,133.92 542.68 271.34 1-Nov
client2 11,448.57 228.97 114.49 5-Nov
client3 15,678.30 313.57 156.78 15-Nov 15-Dec
client4 14,763.07 295.26 147.63 1-Dec
client5 31,546.65 630.93 315.47 1-Nov 15-Nov
client6 17,265.40 345.31 172.65 1-Dec 10-Dec
client7 21,889.56 437.79 218.90 15-Nov
client8 29,663.48 593.27 296.63 1-Dec
client9 26,174.25 523.49 261.74
client10 42,000.00 840.00 420.00
client11 25,546.80 510.94 255.47
0.00 0.00 0.00
0.00 0.00 0.00
0.00 0.00 0.00
 
I already answered this on your previous post. Did it not work, or did you
have questions?

REPOST:
To clarify, you want to find out how much is being paid on a certain date?
Assuming the date you're wanting info on is in cell A2, and your payments
are in B column:
=SUMPRODUCT(((L2:L100=A2)+(N2:N100=A2))*(B2:B100))
 
It did not work even after I changed my spreadsheet to fit the formula you
posted. possibly my question was asked wrong, yes I am trying to find out how
much I get paid on certain dates, and for the commission amounts to be added
into a column of dates. the error I keep getting with your formula is wrong
date type.
 
Relooking at how you have your table setup, if the actual data begins on row
4, the formula will need to be adjusted not to include the header rows.
I.e., if data begins on row 4, change formula to:
=SUMPRODUCT(((L4:L100=A2)+(N4:N100=A2))*(B4:B100))

The problem being that if there is a word in the B2:B100 array, Excel
doesn't know how to multiply words, and spits out an error.
 
When you are referring to A2 - I have dates in the A column - I have tried
both ways, subsitute A2 for date written in A2? or refer to whatever is
written in A2 by referring to A2?
 
You can just use "A2" (sans quotes) to refer to the contents of A2. The date
format though needs to be the same as the dates in columns L and N.
 
I used the formula, changed everything like you said and it works until you
get to the 4th month then starts duplicating the figures from the prior
month.

=SUMPRODUCT(((J2:J30=A20)+(L2:L30=A20))*(K2:K30))

Is the array Im using.

Thanks in advance.
 
Back
Top