Sumif & counta combined

**kashaikh78** · 07-01-2013, 05:29 AM

Hi,

I have a situation where I want to count number of products produced by each factory in Period to date, Last year to date and budgets.....

Column A contains name of product, Column B contains name of Factory where individual product produced. Column C to E contains the volume produced in each factory in month to date, last year to date & budget.

The problem I have is some products produced in current month but not necessary produced in last year therefore column C & D have variable blank cells.

I want to create a table that shows no of products produced by each factory in MTD, YTD & so on...

I think COUNTA & SUMIF combined formula can work.

Is there any one can help

**martindwilson** · 07-01-2013, 05:38 AM

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**kashaikh78** · 07-01-2013, 06:14 AM

I can't upload the file from my work PC, it is restricted for security reasons...Is there anything you can help without having the sample file? otherwise I will attach the mock spread sheet when I go home in the evening.

Thanks

**XOR LX** · 07-01-2013, 06:27 AM

Hi,

Have you tried a Pivot Table?

Regards

**kashaikh78** · 07-01-2013, 06:39 AM

Yes, I have tried Pivot table. It's very manual process as there are so many factories, I have to filter each factory on the pivot table and enter the amount in my table manually for MTD, LYTD & Budgets to date figures. I need more automated results.

**XOR LX** · 07-01-2013, 06:58 AM

In that case, best to wait for this mock spreadsheet as it will be very difficult to give you an answer without seeing the layout.

Regards

**kashaikh78** · 07-01-2013, 07:13 AM

okay thanks...

**kashaikh78** · 07-01-2013, 06:35 PM

Hi,

I have attached the mock spread sheet. I want that excel match the factory name in column B in data tab to the factory name in Column A in Summary tab, count the number of non blank cells in column C and return the result in column B in summary tab.

Thanks in advance for the help.

**martindwilson** · 07-01-2013, 07:00 PM

is this when the filter is on or off? ps there is no sheet called data!

**kashaikh78** · 07-02-2013, 04:15 AM

The spread sheet I attached is has sheet3 instead of data sheet. You can take the filters out. Excel formula should returns the same results as if I use the filters on column B. For example If I filter column B by Factory A and use the formula CountA(C:C) the result would be 39.