# multiple row comparison and addition

• I need to be able to compare values in a number of rows and return the total sum of matches. Two problems,
1st, the number of rows to compare from will always be increased and the count needs to be done real time
2nd, Im guessing its going to be a nested function of some kind - but the ones I have researched (vlookup etc) only appear to compare one column at a time

Eg
Columns A - F have detail on sheet 1
Sheet 2 needs totals of Every Column C with values (sheet 1) that matches criteria as well as column F (again sheet1)

I have searched the old threads and am none the wiser, but Im thinking maybe countif nested ? Another problem is Im almost sure I cant compare amongst multiple sheets so if thats true, I could still use help with the formula, but I will just work on Sheet 1 to get results, and copy to sheet 2

Any ideas

• Does

=SUMIF(C:C, "criteria") + SUMIF(F:F, "criteria")

Work?

If you don't want all of the values from C and F searched - search this forum for "Dynamic Named Ranges" and that could help you come up with a changing range size.

• Still Stuck

sumif didnt work. It returns a null value - is it because criteria im checking is text

My equation is

=sumif('sheet3c:c,'sheet2a1')+sumif('sheet3f:f,"sick") - with all correct punc, cant remember when I typed up

Basically I have a list of staff in column C, with column F containing if sick, late, holiday, etc
Need to count how many sick, late, holiday each member of staff has had and is logged in column F

Any more ideas

• Hi,

Sounds like

=SUMPRODUCT((Sheet!C1:C1000=Sheet2!A1)*(Sheet3!F1:F1000="sick"))

Jindon

• the man's a magician

That works fine. I can also modify this for 75% of the work that needs doing on the sheet.
Many thanks

Stick around though, as Im sure I will be posting several more problems on this sheet alone all next week.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!