# IF OR SUMIF FORMULA - MULTIPLE SHEETS & COLUMNS...Can it work?

1. ## IF OR SUMIF FORMULA - MULTIPLE SHEETS & COLUMNS...Can it work?

I want to know if there's a formula for the following--
Basically -- on sheet 2 for the name of an insurance, I want it to take the sum total for the same insurance on sheet 1.
I have 4 agencies I will need to create spreadsheets for. Not all insurances are in every agency. So I'm hoping there's an easy way to have a formula match the contents of the cell instead of having to put in a formula with every single insurance name (there's over 200).
Hope I've explained it well enough. :D

Thanks for any help!
Jen

2. ## Re: IF OR SUMIF FORMULA - MULTIPLE SHEETS & COLUMNS...Can it work?

yes, try
=SUMIF(Sheet1!H:H,Sheet2!A11,Sheet1!K:K)
BUt not sure which column you want to add up, i have chosen K
but change that column to what you want to sum

3. ## Re: IF OR SUMIF FORMULA - MULTIPLE SHEETS & COLUMNS...Can it work?

Thank you! It's even super simple! I was making it way too hard.

4. ## Re: IF OR SUMIF FORMULA - MULTIPLE SHEETS & COLUMNS...Can it work?

Hi -

I think this is pretty straight forward, unless I'm missing something. I'm using your highlighted example for the general formula. So on Sheet 2 enter this formula in cell B11:

=SUMIF(Sheet1!\$H\$2:\$H\$27,Sheet2!A11,Sheet1!\$J\$2:\$J\$28)

This will sum up the Balance Column for MDHHS.

Just copy the formula up and down to get the sums for each of the other names.

Hope this helps.

5. ## Re: IF OR SUMIF FORMULA - MULTIPLE SHEETS & COLUMNS...Can it work?

you are welcome

6. ## Re: IF OR SUMIF FORMULA - MULTIPLE SHEETS & COLUMNS...Can it work?

=SUMPRODUCT(SUMIF(INDIRECT(CONCATENATE(_sh,"!H2:H27")),\$A11,INDIRECT(CONCATENATE(_sh,"!J2:J27"))))
_sh is a name for sheets to consolidate

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1