You are viewing limited content. For full access, please sign in.

Question

Question

Forms report download, need Excel sumif help

Forms Version 10
Updated February 4, 2019
asked on February 1, 2019

Anyone out there an Excel expert?  

I've downloaded a Laserfiche Forms Report to Excel, but I need it to be able to tally something like this (simplified version shown below), maybe using the Excel sumif function?

Can I use Excel functions to have it find every reference to apples and/or oranges and have it tally the numbers for me on another Sheet?  


Here's what I'm up against:  The following is a section of the actual report from Laserfiche on the trees ordered that I would like to have compiled into an order without someone having to do it manually:

0 0

Answer

SELECTED ANSWER
replied on February 1, 2019

That shouldn't be too difficult

For example,

=SUMIF(B1:B100,"apples",C1:C100)

=SUMIF(B1:B100,"oranges",C1:C100)

When you put it on a different sheet, you just need that references in the function. For example, if my data is on "Sheet 2" and my totals are on "Sheet 1" it would look like this

=SUMIF('Sheet 1'!B1:B100,"apples",'Sheet 1'!C1:C100)

But, the easiest way is to just create it on the data sheet, then cut and paste it to where you want it displayed because Excel will update the formal for you.

 

1 0
View 5 previous replies
replied on February 1, 2019

Thanks, Jason!  I've been able to make a piece of that work, testing on Sheets 2 and 3.  I'm pretty sure now I can expand this to cover all the different types of trees it needs to collect!  Awesome!

0 0
replied on February 1, 2019

To make things easier, if your label column matches the text exactly, you can set just that for the comparison value instead of typing it manually for each one.

For example,

=SUMIF(Sheet2!G1:G100,A1,Sheet2!H1:H100)

1 0
replied on February 1, 2019 • Show version history

Thanks, Jason.  I was trying to get that to work.  I thought at first it didn't work because the A1 (as you show above) is on the other Sheet.  The G1:G100 and H1:H100 cells are on Sheet 2 and the cell I wanted it to pull the name of the tree from was on Sheet 3.

However, my final test today just worked!

Sheet 3:  

0 0
replied on February 1, 2019 • Show version history

Hi Connie,

The A1 is based on the screenshot you provided in the post just before that one.

In this example here, if you replace "test" with A6 (no quotes) it should work because they are the same value.

For example

A2 = "Souris Raspberry"

A3 = "Darn Gorgeous Rose" 

etc.

The idea is that your reference value should be a static cell on the same row as the calculation, not where the data is coming from.

 

0 0
replied on February 1, 2019

Thanks, Jason, I confess I did "edit" my post.  I did have a screenshot there with the word "test" in the formula and I decided to try the cell location again and it worked so I changed my post to reflect that the cell reference in that spot does actually work.

0 0
replied on February 1, 2019

Okay cool!

0 0
replied on February 2, 2019 • Show version history

Connie

In your cost/price calculations you can also avoid the #DIV error by using an IF function

IF(Condition,Value if True,Value if False)
=IF(B2>0,(Calculation Here),0)

Do the first calculations in C2 and D2 and then do a "Fill Down" on the rest of the rows.  You may need to go back into each and fix the calculation section of each cell.

1 0
replied on February 4, 2019 • Show version history

Thanks, Bert!  I was able to make that work, as well!

 

0 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...