Sumif from multiple sheets

**h_aesa1** · 07-20-2010, 07:03 PM

I am trying to sumif on a Totals Sheet in this workbook.

The problem I am having is that I am trying to calculate 4 different sheets to get to the total number.

Before the sheet was combined with all the reps on 1 sheet but it was much easier to sort and see by creating individual sheets.

I have attached the workbook with a couple of comments.

thanks for any help,

JJ

**rethguals** · 07-20-2010, 08:30 PM

A4 : Sold -> Open
B4 : =SUM(SUMIF(INDIRECT("'"&{"Rep 1";"Rep 2";"Rep 3";"Rep 4"}&"'!F2:F65536"),A4,INDIRECT("'"&{"Rep 1";"Rep 2";"Rep 3";"Rep 4"}&"'!E2:E65536")))
with [Ctrl + Shift + Enter]

**Marcol** · 07-20-2010, 08:36 PM

Try this

In Sheet "Totals"
1/. Create a validation list in A3 with all the sheet names (Rep 1, Rep 2, etc..........)

2/. In B4

Please Login or Register  to view this content.

3/. In B5

Please Login or Register  to view this content.

Hope this is what you are after

**Marcol** · 07-20-2010, 09:01 PM

I might have misread your question. I read your comments on the worksheet and didn't look at your post again

However this might be of interest

I hope it helps

**Marcol** · 07-20-2010, 10:13 PM

Or maybe just create named ranges

Rep1_OC, Rep2_OC,Rep3_OC, Rep4_OC
Rep1SQC, Rep2SQC,Rep3SQC, Rep4SQC

then

A4 ="Open"
A5 ="Cancelled"

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hope this helps

**h_aesa1** · 07-20-2010, 10:52 PM

thank you fellas... looked at both solutions and both work awesome. For right now, because I already have it designed, i will use Rethguals but will make use of Marcol's solution so I can break it out by rep when i get some time....

....1 question

how would i write a formula (using Rethguals logic or formula) to do a countif instead of sumif

example.... count number of times that the word open occurs or cancelled occurs??

thanks

JJ

**Marcol** · 07-20-2010, 11:44 PM

This should be enough

A4 ="Open"
A5 ="Cancelled"

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I have added rethguals formulae and the above to the attached workbook
and deleted some lines so the individual reps totals differ

Hope this helps

**h_aesa1** · 07-21-2010, 12:31 AM

works great...thanks for the help!!

JJ

**Marcol** · 07-22-2010, 11:27 AM

In response to your PM

Hi Marcol,

I didn't know if I should create a new post....

I actually decided to use your method, more informational...

How do I use your method and do the countif instead of sumif, if you don't mind helping me.

thanks and appreciate your time and help

JJ

I have updated this attachment.
This workbook does both countif and sumif conditions and for your conveniance includes rethguals' array formulae.

Hope this helps