CODEWITHSUNDEEP
excelexcel-formula
l3echod
l3echod

Reputation: 25

Excel formula to get specific data that according to another column

I have a spreadsheet that contains various sheets, each sheet contains different types of orders including items, descriptions, quantity, etc.

ITEM    DESCRIPTION    QTY
Apple    ...            1
Orange   ...            4

I would like to get a formula that is capable to find out the total quantity of items from every sheet.

Upvotes: 1

Views: 74

Answers (2)

Jon
Jon

Reputation: 224

If all your sheets with "data" on them are the same format, you can save time and maintenance by doing the following:

create a sheet called start which is before all the "data" sheets. This sheet should be blank (except perhaps for a bit of text saying "this sheet is intentionally blank")

create a sheet called end which is after all the "data" sheets.

Then in your summary sheet, you can do =SUM(start:end!C:C) (I'm assuming your quantity is in column C from your example above).

If you use this approach, you can easily add sheets in between start and end; the formula will not need to be rewritten

Per @l3echod's comment in another answer, you can also use this pattern in SUMIF and SUMIFS formulae, if you want total quantity per item:

=SUMIF(start:end!$A:$A,$A2,start:end!$C:$C)

this assumes the item in question is in cell A2. Note that depending on the volume of data you have, this might be a slow formula to calculate.

Upvotes: 1

WNG
WNG

Reputation: 3815

If you want to do it in a single cell, I'm afraid the only way of doing so without VBA is to make a manual search over all the pages :

= SUMIF(Page1!A:A,"=ItemName",Page1!C:C)+ SUMIF(Page2!A:A,"=ItemName",Page2!C:C)+ ...

Other solutions would involve you creating your own personal vba function or adding new cells in each sheet which I'm not sure are within the realms of possibilities.

Upvotes: 1

Related Questions