Excel Function Sumif/ Sumifs or Sumproduct

[Sontu]

Hi all,

I have a spreadsheet that looks similar to the one attached. I am unable to think of a way to get excel to summarize the product of units with the count under the coded items such as PPO-1, PPO-2, etc under each month. Is there a formula that can sum the product of units and the count and summarize it in the summary table based on their codes and month?

Any suggestion will be of great help.

Thanks

Sontu

[tim201110]

=SUM(IFERROR((OFFSET(Details!$D$7,,E$2,100,E$3)=$C8)*OFFSET(Details!$D$8,,E$2,100,E$3),)) CSE

[Glenn Kennedy]

Like the solution above, this is an array formula. but it avoids the use of the volatile OFFSET function, which can slow things down.

=SUM(IF(IFERROR(MONTH(Details!$E$3:$NQ$3),0)=MONTH(1&Summary!E$4),IF(Details!$E$7:$NQ$47=Summary!$C5,Details!$E$8:$NQ$48)))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[tim201110]

OT
Glenn,
i find it funny (bad word, excuse my poor English) to use this here:

volatile OFFSET function, which can slow things down

arrays in post #2 are 12 times less, no data conversion, no problems with localization,
besides, there is not any NOW() in the file, usedrange is not huge.
All info concerning so called Volatile Functions is quite old. And I was taught to avoid using them. But they are fast, short and comps and Excel are very powerful nowadays.
Don't you think so?

[Sontu]

Thanks Tim and Glenn,

Both the formulas are working to get the sum of counts of each code, however i was hoping if they could yield the sum of the multiplication result of the count with the units. For eg. for PPO-1 it will be the sum of the product of units under Specimens 2, 9, 10 and 12 with the respective counts, i.e. 20 x 5 (Specimen 2) + 10 x 1 (Specimen 9) + 10 x 4 (Specimen 10) + 10 x 8 (Specimen 12) = 230 against PPO-1 in Summary for January. Is this possible with a formula that can be dropped down to apply across and along?

Sontu

[Glenn Kennedy]

Hi. This was not so easy.... Your original sheet is flawed. Open "original" and scroll to BQ 6 & 7. the data are in the wrong place. The risk of this happening was greatly increased by your introducing a number of empty rows, and then forgetting that they were meant to be empty. To prevent this from happening again, I deleted the three empty rows between the headers and the data. Also, since all of March's data was offset by one row, as a result of this mistake, I deleted March data. Jan and Feb are fine. I accidentally deleted your data validation, too (apologies for that). But I'm sure that you can put it back.

Anyhow. A monster formula can do what you want. NOTE: The ranges in the first two INDEX statements are NOT the same as those in the 3rd and 4th INDEX statements. This was INTENTIONAL.

Please Login or Register  to view this content.

This is an array formula.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Glenn Kennedy]

Mis-post removed by GK

[Sontu]

Hi. This was not so easy.... Your original sheet is flawed. Open "original" and scroll to BQ 6 & 7. the data are in the wrong place. The risk of this happening was greatly increased by your introducing a number of empty rows, and then forgetting that they were meant to be empty. To prevent this from happening again, I deleted the three empty rows between the headers and the data. Also, since all of March's data was offset by one row, as a result of this mistake, I deleted March data. Jan and Feb are fine. I accidentally deleted your data validation, too (apologies for that). But I'm sure that you can put it back.

Anyhow. A monster formula can do what you want. NOTE: The ranges in the first two INDEX statements are NOT the same as those in the 3rd and 4th INDEX statements. This was INTENTIONAL.

Please Login or Register  to view this content.

This is an array formula.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Thanks Glenn. I realised the errors in the sheet after the post as it was prepared in a haste. I appreciate you taking the time to get this monster formula. It does the work.

I have been trying to work with SUM/ SUMIF and SUMPRODUCT and could only get the worksheet to give me results multiplied by the count of the value i am looking. For instance; for PPO-1 it is yielding 950 (which is 190 x 5). I could get it to give 190 however it will yield #DIV error for values that do not exist. Attached a sample.

Thanks
Sontu