Excel 2007 : count number of transaction in a month when date is entered, also total

I'll try to get you started, can't stay online
To count the number of months in an array ( say g2:g12) try
=SUMPRODUCT(--($G$2:$G$12<>"");--(MONTH($G$2:$G$12)=1)) for January
replace 1 with 2 for February, etc...

It looks like you can use:

Cell C30:

=SUMPRODUCT(--($E$2:$E$12<>""),--(MONTH($E$2:$E$12)=(ROW()-ROW($A$29))),--(YEAR($E$2:$E$12)=A$28))

and:

Cell D30:

=SUMPRODUCT(--($E$2:$E$12<>""),--(MONTH($E$2:$E$12)=(ROW()-ROW($A$29))),--(YEAR($E$2:$E$12)=A$28),--($F$2:$F$12))

Drag down for months 2 to 12.

The first formula will count the dates in each of the months (column E) and the second will sum the bonus for those months (column F).

If you need to include values from other columns, just repeat the formulae but change the columns to which they refer.

Regards

Thanks Again to TMSHUCKS!
The last formula you sent is working. Thanks so much for your patience!. I did try just playing with the cell numbers a bit, but I could just not get it. This great!
Never thought I'd be so excited over a spreadsheet.

I do have one last question, Pretty Please.
Based on your quote below I should just be able to change the column letters if I want to total a different column. However to total the bonus, I pasted the same formula, changing the column (for example...=SUMPRODUCT(--($F$2:$F$27<>""),--(MONTH($G$2:$G$27)=(ROW()-ROW($A$30))),--(YEAR($G$2:$G$27)=F$29)). After Sumproduct I changed G2:G27 to F2:F27.
This give a count and not a total.

Thanks for all you do!

Corey



"The first formula will count the dates in each of the months (column E) and the second will sum the bonus for those months (column F).

If you need to include values from other columns, just repeat the formulae but change the columns to which they refer."

Regards[/QUOTE]

I'm guessing the zero return you are looking at is because there are no 2009 dated transactions.

Yes, if you extend the data to row 27?, you'll need to adjust the formula. Looks like the table goes to row 26 to me but it wasn't obvious from the original sample ... sorry.

And, yes, you need to replicate the formula for additional column pairs.

So, cell *H30* would become:

=SUMPRODUCT(--($G$2:$G$26<>""),--(MONTH($G$2:$G$26)=(ROW()-ROW($A$29))),--(YEAR($G$2:$G$26)=F$28))     +     SUMPRODUCT(--($L$2:$L$26<>""),--(MONTH($L$2:$L$26)=(ROW()-ROW($A$29))),--(YEAR($L$2:$L$26)=F$28))     +     SUMPRODUCT(--($Q$2:$Q$26<>""),--(MONTH($Q$2:$Q$26)=(ROW()-ROW($A$29))),--(YEAR($Q$2:$Q$26)=F$28))     +     SUMPRODUCT(--($V$2:$V$26<>""),--(MONTH($V$2:$V$26)=(ROW()-ROW($A$29))),--(YEAR($V$2:$V$26)=F$28))

This is for January, 2010, count of posting date column. Drag down for the rest of the months. Copy and past the formulae for 2009, 2011, 2012 and 2013.

You will need similar extensions for the budget totals.

Regards

Hi Russell,

Another way to do this problem is with Pivot Tables. I moved your data around a bit and created two pivot tables so you can see how simple they are. See the attached.

I want to thank all who have tried to help.

The pivot table is interesting, but I just don't understand how to create one. the one you provided is very helpful thanks, Marvin.

to TMShucks, a big big thanks. Again, my understanding is too limited. you were kind enough to write the formula for me, but I still can't seem to make it work. The counts are coming back wrong.
I have header rows in my table and it seems to have throws off a few of the numbers by one. when I adjust them the counts are still wrong.

In (MONTH($G$2:$G$26)=(ROW()-ROW($A$29))) , what is the ($A$29) for? in my table that cell contains "2009" could that effect counts?

Thanks Again, you're awesome!

Hi Russell,

Part of my concern is that you had your data in an inefficient form. You have Name and Start Date in columns A and B. Then you have 4 sets of 5 columns all with the same data.

I moved your 2nd, 3rd and 4th sets of data DA #2, DA #3 and DA #4 under the DA #1
I then copied the Names and Start Dates down to match

The reason for this is to have a "Table" of data that pivot tables work well with.

The result I gave you and it works great with Pivot Tables.

Put your cursor in the Pivot table and play with it a little. Read the help.

Many times the way you keep your data allows Excel to work with it better.

OK, in the original, relatively simple formula, after adjustment to cater for the extra row, you would have had, in cell H31:

=SUMPRODUCT(--($G$2:$G$27<>""),--(MONTH($G$2:$G$27)=(ROW()-ROW($A$30))),--(YEAR($G$2:$G$27)=F$29))

To break this down, $G$2:$G$27<>"" checks that cells G2 to G27 are not blank.

MONTH($G$2:$G$27)=(ROW()-ROW($A$30)) compares the month number in the cells G2 to G27 to a calculated value based on the relative position of the rows ...

ROW()-ROW($A$30) generates numbers from 1 to 12; for example, this formula in cell H31 is equivalent to 31-30=1, in H32, it is equivalent to 32-30=2.

YEAR($G$2:$G$27)=F$29 checks the year in cells G2 to G27 is equal to the value in cell F29 ... which is 2010.

The SUMPRODUCT generates three arrays of zeroes and ones, multiplies them together and sums the total. In this case it's just a count.

The difference with the second simple formula is the addition of ($F$2:$F$27) on the end

=SUMPRODUCT(--($G$2:$G$27<>""),--(MONTH($G$2:$G$27)=(ROW()-ROW($A$30))),--(YEAR($G$2:$G$27)=F$29),--($F$2:$F$27))

This last part contains an array of the bonuses which, when multiplied by the zeroes and ones from the other arrays gives you a total bonus.

OK, so that's the "simple" formula.

The following formula (in H31) does the same thing but four times, one for each set of columns:

=SUMPRODUCT(--($G$2:$G$27<>""),--(MONTH($G$2:$G$27)=(ROW()-ROW($A$30))),--(YEAR($G$2:$G$27)=F$29)) + SUMPRODUCT(--($L$2:$L$27<>""),--(MONTH($L$2:$L$27)=(ROW()-ROW($A$30))),--(YEAR($L$2:$L$27)=F$29)) + SUMPRODUCT(--($Q$2:$Q$27<>""),--(MONTH($Q$2:$Q$27)=(ROW()-ROW($A$30))),--(YEAR($Q$2:$Q$27)=F$29)) + SUMPRODUCT(--($V$2:$V$27<>""),--(MONTH($V$2:$V$27)=(ROW()-ROW($A$30))),--(YEAR($V$2:$V$27)=F$29))

I've left several spaces around the plus (+) signs so that each of the separate elements stands out.

I promised myself I wasn't going to do this ... but the formula in cell I31 to calculate the bonuses across the four sets of columns is:

=SUMPRODUCT(--($G$2:$G$27<>""),--(MONTH($G$2:$G$27)=(ROW()-ROW($A$30))),--(YEAR($G$2:$G$27)=F$29),--($F$2:$F$27)) + SUMPRODUCT(--($L$2:$L$27<>""),--(MONTH($L$2:$L$27)=(ROW()-ROW($A$30))),--(YEAR($L$2:$L$27)=F$29),--($K$2:$K$27)) + SUMPRODUCT(--($Q$2:$Q$27<>""),--(MONTH($Q$2:$Q$27)=(ROW()-ROW($A$30))),--(YEAR($Q$2:$Q$27)=F$29),--($P$2:$P$27)) + SUMPRODUCT(--($V$2:$V$27<>""),--(MONTH($V$2:$V$27)=(ROW()-ROW($A$30))),--(YEAR($V$2:$V$27)=F$29),--($W$2:$W$27)).

I have to say that I don't think you are doing yourself any favours in the way the worksheet is laid out. It would be much more simple and straightforward if you split the input and the analysis across two sheets AND listed the input data in one set of columns rather than four.

As far as I can see, these formulae work but they will be very difficult to maintain and it will be easy to make mistakes which will not be obvious but which will result in erroneous answers.

You may be constrained by the requirements of your management or whatever but, from a practical point of view, I think you should be arguing to restructure your workbook.

As you will see in this forum, there are many people who advocate using pivot tables. If the data were restructured, you could make use of this extremely powerful analysis tool and, quite possibly, get more out of it than a simple count and sum.

That's my advice ... your choice. Good luck

Regards

Wow,
I am so Sorry.
I do really appreciate your help and the break down of what the peices of the formula do.
The purpose of the structure of the workbook (I think) was to keep the data organized by agent and maintain that they have paid bonuses on only 4 transaction. As you can see I do not have the knowledge needed to restructure. The formulas proposed where to cut back on endless hours of manual calculation.
I sounds like what your saying is I have spent all weekend researching and working on this, and waisting my time and yours. I truely apologize, and thank you for your time and knowledge.
Respectfully,
Corey

Just to complete the solution, I have amended and copied the formulae across all the analyses.

It wasn't simply a case of copying the formulae down to years 14 to 18; they needed some of the cell values amending because of the absolute references. So, beware, when you are trying to enhance and improve this workbook in a few months (or years), it might be more work than you think.

Please see the attached workbook.

Kind regards

I have taken your suggestions into consideration and will submit them to management.
Thanks for everything.

anyone know what I need to do to mark this solved?

First FAQ

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Regards

Hi Corey

I think a couple of our posts crossed. There's no need to apologise. For me, it is an excuse to play with Excel and to provide some assistance and support. Your time was, hopefully, not wasted and you have learnt from the exercise ... and will certainly know more now than when you started a few days ago.

Good luck with the redesign ... and, if you don't redesign, good luck with any future changes :-)

Regards