Sumproduct Formula

[tmac]

I am working with Excel for a major fundraiser for our school and am wondering what I can do to retreive certain information without manually going through all data.

I need to know 1)all students that produced 15 flyers and the total amount of pledges, and 2)students that may not have produced 15 flyers BUT have a total pledge of $100 or greater. I would like this information to be transferred to another worksheet in the workbook by itself.

Is this possible? Thanks all for your help! I am slowly learning.

tmac

[NBVC]

=Sumproduct(--(range_number_flyers>=15),range_total_pledges)

=Sumproduct(--(range_number_flyers<15),--(range_total_pledges>100))

replace italicized references with actual ranges in your sheet housing the corresponding data.


Btw, your example.zip is password protected..

[tmac]

I'm sorry but I'm not following. I'm resending the example - didn't know I had it password protected. Thank you very much for your help.

[NBVC]

still asking me for a password....

[tmac]

Try "mickey". I'm looking to delete the password.

Sorry.

[NBVC]

Still not working...


example of formulas in use....

=Sumproduct(--(B2:B100>=15),C2:C100) sums total pledges for students with more that 15 flyers.

=Sumproduct(--(B2:B100<15),--(C2:C100>=100)) counts number of students with less than 15 flyers, but more than $100 in pledges.

where B2:B100 house the number of flyers per each student (that me be in A1:A100) and C2:C100 would be there corresponding total pledge amounts.

[tmac]

Resending. I can't figure this password deal out so I saved it again with the name of "mickey". I'm sorry to be SO much trouble.

[NBVC]

Still no cigar

Try copying the range of data and pasting to an entirely new workbook. Then save that and zip it to me.

[tmac]

I am so sorry. I've copied & pasted into a new workbook so hopefully we'll be smoking now! Thank you for being patient with me and not yelling yet!
tmac

[tmac]

I am so sorry. I've copied & pasted into a new workbook so hopefully we'll be smoking now! Thank you for being patient with me and not yelling yet!
tmac

[NBVC]

Are you sure that your winzip is not somehow putting a password....it still asks for a password. I've opened many other zip files in this forum and none have asked for a password...so it's definitely not my end.

[tmac]

Sorry. I have searched and did not have any password on my zipfiles. I HAVE now added a password to this attachment seeing if that will eliminate the other problem. The password is "mickey". As I said, I am very sorry for the trouble. No one else has ever had a problem opening my zip files but they were not posted on a forum either.

Thanks very much.
tmac

[NBVC]

Still no luck ...

Here's a sample I created that hopefully will help you....

Hopefully it helps you understand..

just replace the ranges in my formula to correspond to your ranges.

[tmac]

Sorry this has been so much trouble. I have looked at your sample and question: Since I have in one column student's name and if they had 15 flyers their name would be listed in 15 individual rows under same column to provide sponsor name, address, etc.

# Student Name Sponsor Name Address Pledge Paid
1 John Smith Sally 234 smith road $25 $25
2 John Smith
3 John Smith
4 John Smith
etc.

With your sample - are you suggesting I will have to manually go through the spreadsheet & list on another sheet or column student's name once & then a column for the total number of flyers they had in order to use your formula?

I appreciate the time you have spent on this never ending project.

tmac

[NBVC]

I think that you should have a list of unique student names on another sheet and then you can apply formulas that will summarize the other 2 columns....Then you can use my original formulas to give your ultimate results.

See sample sheet attached.

An original list of pledge details is in Sheet2.

In Sheet1, I list the unique names, and I have a formula in B2 to count flyers (based on occurrences of the person's name in Sheet2). I have a formula in C2 to sum the total amount of pledges for the corresponding student.

Note: For this sample I only worked with Jon and Mark....but you get the idea.

just adjust the ranges to match the ranges in your details sheet and copy the formulas down the columns.

The original formulas (also with adjusted ranges) should apply to your summary sheet.

[NBVC]

Here's the sample sheet....