# Help with Excel formula

• 2012-11-19, 09:17
DPMarketing
Help with Excel formula
I am working on a large spreadsheet that I cannot figure out the formulas for.

I am trying to set formulas for orders that have been placed for individuals each month and have the speadsheet configure totals of responses that came in from those mailings for each month for each state.

Column C are the months the orders were placed
Column J are the states for where these orders were placed
Column P are the responses that came in from these mailings

Thank you,
Tina
• 2012-11-19, 09:26
RetiredGeek
Tina,

Welcome to the Lounge as a new poster. :cheers:

It would be very helpful if you could post a sample workbook with made up data on one sheet and the data and expected results (manually calculated) on a second sheet. It is hard to tell just what you want from the description above. :cheers:
• 2012-11-19, 09:49
DPMarketing
Good Morning RG,

Thank you for the warm welcome!

I've attached my file.

Thank you,
Tina
• 2012-11-19, 09:57
sdckapr
For the number of replies in Nov from CA you can use a formula like:
=SUMPRODUCT((\$C\$2:\$C\$26="Nov")*(\$J\$2:\$J\$26="CA")*\$ P\$2:\$P\$26)

Adjust the ranges as desired. Also instead of the explicit text in the formula, you could refer to a cell if desired.

A Pivot Table could give the results as well.

Steve
• 2012-11-19, 10:36
DPMarketing
Good Morning Steve,

Oh my gosh...............THANK YOU!!!!!!

Now, I have another question. What if I wanted to have my formulas on Sheet 2? I know how to do the sumif function, but this is now far beyond me.

Thank you,
Tina
• 2012-11-19, 10:41
DPMarketing
Hi Steve,

Oh my gosh................Never mind, I got it and figured it out.

Thank you so very much! :)

Tina
• 2012-11-19, 12:36
sdckapr
You could use sumifS:
=SUMIFS(Sheet1!\$P\$2:\$P\$26,Sheet1!\$C\$2:\$C\$26,"Nov", Sheet1!\$J\$2:\$J\$26,"CA")

if that is more clear than SUMPRODUCT [I often revert to formulas that are pre-XL2010 as I have been using them since XL97 before some of the newer formulas]

Steve