1. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer' 2. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more? # SUMIFS Value Error

When you use SUMIFS in a formula you may discover that your get a SUMIFS value error. Normally a #VALUE error means that one of the references is not a true value in Excel (perhaps has a space in it or is a word) but in this case the reason is different and more importantly it acts differently to the normal SUMIF.

## SUMIFS situation with #VALUE errors

You will see in the image below that the SUMIFS (cell C8) has generated a #VALUE! error. This is due to the Sum range and the Criteria range not matching i.e. the sum range is C24 to D48 (2 columns and 24 rows) but the criteria range is B24 to B48 (1 column and 24 rows).

With a SUMIFS this results in a #VALUE! error. Interestingly, if you make the same mistake with a normal SUMIF you get no error, it does a calculation. So in the example above (cell D8), although the SUMIF also refers to 2 columns and 24 rows versus 1 column and 24 rows it generates an answer.

In conclusion, when using a SUMIFS (and a SUMIF), make sure that the Sum Range and Criteria Range are matching sizes and shapes.

SUMIFS returns value when linked workbooks are closed

Sum all the negative numbers in Excel

Use of IFERROR with VLOOKUP

Online Excel Course Recommended for you