Multiple IF formulas in one cell (NOT nesting)

[elitarvin]

I have tried searching google and this forum, and I'm probably just not using the correct wording. So I apologize if this is a repeat question, however I'm just a padawan seeking a jedi answer! The best way for me to get my need across is an example:

Let's say I have column A and B. If column A equal "Smith, John", then I want it to insert "$100" into column B. But it's not just that, I need it to do that for more than one customer and the column B might be a different value. So lets say column A equals "Doe, Jane" then I need it to enter $150 into column B. I use a spreadsheet for my accounting for my small business and this will make data entry much quicker. This will be used for inputting cusomter payments and adding the correct values into price charged, tax collected, etc columns that I have to insert manually each time I receive a payment. The amount never changes for these items so if I can auto populate these fields whenver I insert a customer's name into column A, that would be excellent!

Thanks in advance!

[tamthat]

I think you should upload your file and we will help you!.

In your case, you have create a database include all customer name with values. And then, you use VLOOKUP formulas to lookup with customer name!

[elitarvin]

Thanks for the reply! This seems like it will be slightly beyond my scope. I will just upload my file

If customer's name (all are listed on sheet "Payments - Weekly Service" column A) is entered into Column C on sheet "Report" AND "Weekly Cleaning Service Renewal" is entered into column D on sheet "Report", then I want:
- Column G from sheet "Payments - Weekly Service" into column H, I and M on sheet "Report"
- Column H from sheet "Payments - Weekly Service" into column L on sheet "Report"
- Column C from sheet "Payments - Weekly Service" into column J on sheet "Report"
- Column D from sheet "Payments - Weekly Service" into column K on sheet "Report"

There are some variations I can take care of once I see the database and everything. I'm excel savvy enough to tinker with the formulas and database and placements etc. This would be incredible! So many thanks!

I'm not having any luck uploading the file. The attachment button just pops open a small blank white box. I tried it on two different browsers. I just uploaded it to a file hosting site here:

http://www.filedropper.com/2016-etpools-report

[JohnTopley]

To Attach a File:

1. Click "Go Advanced"
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[elitarvin]

When clicking on the attachment button, it only pops up a small blank white box. It does not actually let me attach anything. And i disabled ad blockers and tried two different browsers.

[FlameRetired]

Hi elitarvin and welcome to the forum.

That button hasn't worked for some time. That's why JohnTopley posted instructions.

While file hosting sites are nice many contributors are reluctant to down load from 3rd parties ... too many unknowns.

You're likely to get more participants and faster solution if you try again to upload as above.

Thanks,
Dave

[elitarvin]

I'm so sorry ya'll! I'm a bit acattered brained today. Doing many thing at once right now. I found the Manage Attachments link! I was looking for an actual button. A bit confusing. Thank goodness it wasn't a snake! Sorry about that. Here's the file! And thanks again for all the help already!

[FlameRetired]

Let's do this one item from the list at a time.

For

- Column G from sheet "Payments - Weekly Service" into column H, I and M on sheet "Report"

put this formula down columns H, I and M of 'Report'. You will likely have to copy and paste one Monthly sub-section at a time. Let us know if this gives you a start.

Please Login or Register  to view this content.

For this part

=IF(COUNTIF($D3,"*Weekly Cleaning Service Renewal*"),SUMIFS('Payments - Weekly Service'!$H:$H,'Payments - Weekly Service'!$A:$A,Report!$C3),"")

in column L of 'Report'.

Please Login or Register  to view this content.

Again do the copy / paste for each monthly sub-section.

That takes care of the $$ parts. The rest is text. I will need to spend some time mapping out the formula(s) for those and post back.

In the meantime does this do what you want?

PS On second thought the first formula can just be done on column H of 'Report' then in columns I and M

Please Login or Register  to view this content.

and fill down. It saves triplicate calculations.

[FlameRetired]

Now do this in J3 fill down and across column K. This takes care of the names.

Please Login or Register  to view this content.

[elitarvin]

I think that did it! I'm going to play around with it a bit more to make sure. It's so beautiful! Lol. Will I need to adjust the formula when I add more customers to the "Payments - Weekly Service" sheet or does it cover any future changes like additions of rows?

[elitarvin]

I just remembered too that I also have a "Biweekly Cleaning Service" item name. Is there a way to add that to this part "=IF(COUNTIF($D30,"*Weekly Cleaning Service Renewal*")"? Like an "or" statement?

Edit: Scratch that, I think what I need is a bit more complicated. I need it to pull from columns L and M on the payments sheet if I enter "biweekly cleaning service renewal" in the report.

[FlameRetired]

I think that did it! I'm going to play around with it a bit more to make sure. It's so beautiful! Lol. Will I need to adjust the formula when I add more customers to the "Payments - Weekly Service" sheet or does it cover any future changes like additions of rows?

You might need to extend the ranges in the text formula depending upon how many additions you make. Down to row 1000 should cover quite a bit. Do resist the temptation to reference whole columns in that formula though.

The first formula (SUMIFS) can take whole columns without detriment. There should be no need to edit that. The ranges are covered.

Glad to know it looks promising. Keep me posted.

[elitarvin]

Thank you so much for your help already! You guys are impressive! This is exciting (I don't get out much)!

[elitarvin]

The formula is great! I took out the asterisks so that only if I type in Weekly Cleaning Service will it add prices. However I do need a separate formula for "Biweekly..." which pulls from columns L and M on the payments sheet

[FlameRetired]

OK.

If you aren't aware of it the COUNTIFS family of functions will accept wildcards. That includes SUMIF(S) and AVERAGIF(S). I used those to include variations on 'Weekly Cleaning Service'.

We can pull from L and M on the payments sheet. In which columns did you want the formula(s) output?

[elitarvin]

Sorry for the delay! The first formula is great. I need to add L column from payments sheet to H, I and M on the report sheet. And put M from payments sheet to L on report sheet. Both of these if "Biweekly Cleaning Service" is entered on column D on reports sheet. So just like the first formula, but pulls from L and M if "Biweekly" is typed instead of just weekly. So just expand the formula to include "Biweekly" and have it pull from those two new columns if thats the case.

[elitarvin]

Alrighty, I made some changes to some things, so I figured it would be best just to kind of start over on what is needed now. I have uploaded latest file.

If the name from sheet "Payments - Cleaning Service" column A is entered in column B on sheet "Sales and Use Tax" AND "Weekly Cleaning Service Renewal" is entered into column C, I need it to insert column "I" from "Payments..." into column D and E on sheet "Sales and Use Tax". If "Biweekly Cleaning Service Renewal" is entered instead, I need it to enter column L from "Payments..." into column D and E on sheet "Sales and Use Tax".

We will tackle the other things afterwards! Thanks so much! Y'all are great!

[FlameRetired]

Try this formula in column D 'Sales and Use Tax',

Please Login or Register  to view this content.

Then in column E

Please Login or Register  to view this content.

[elitarvin]

Worked great! Just one question out of curiosity. Why does it put "#N/A" in the cells with that formula? Is there anyway to hide that or change it? NOT a big deal at all, jkust figured I would ask.

Alrighty, next thing I would like...
Every month I total out the columns on the "Sales and Use Tax" sheet, so let's just say that row 100 is the totals row for February. Once column C on "Sales and Use Tax" has "February Totals" typed in it, I would like for it to automatically add all the cells from columns D, E and F, (not ALL of them, just for the month of february, so from January Totals" to "February Totals") and then I would like it to enter column D, E and F from row 100 into B4, C4 and D4 on "Totals" sheet. But the tricky part is that it might not be row 100 on sheet "Sales and Use Tax", it could be anything. So I need a formula that will enter those totals onto the "Totals" sheet automatically, no matter what rows everything is on.

I hope that makes sense!

[FlameRetired]

It returns "#N/A" because it did not find a match for one or more criteria. I didn't know how you wanted to handle those. They can be hidden by wrapping IFERROR around the formula.

=IFERROR(SUMIF('Payments - Cleaning Service'!$A:$A,B2,CHOOSE(MATCH(C2,{"Weekly Cleaning Service Renewal","Biweekly Cleaning Service Renewal"},0),'Payments - Cleaning Service'!I:I,'Payments - Cleaning Service'!L:L)),"")

[elitarvin]

It worked perfectly. Thank you.

[FlameRetired]

You are welcome.

[elitarvin]

Well I think I found a work around on my own. I uploaded the final file if anyone is curious to what I did. I thank you so much for all the help everyone and will keep you in mind and spread the word for other issues. Have great ones!

[elitarvin]

I would like to add one more thing, but I'm having trouble piecing it together using the other formulas. I essentially need...

If any cell in column A sheet 1 equals any cell column A in Sheet 2 *AND* column B sheet 1 equals either "text example 1" or "text example 2", insert "Yes" into the cell this formula is located.

Thanks in advance!